move cell contents

Is there a way to move a cell contents to another cell with a formula.  ex: 
if a5="Name" then move g5 to j5?  Also, I am using 
=INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list.  
I have the formula in different place pick random names from different list.  
This does work, but I have different list with some of the same names and 
with the random pick I do not want the same name to appear.
-- 
Thanks for any and all help.  
Davidl
0
DavidL1 (20)
4/12/2005 4:37:02 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
510 Views

Similar Articles

[PageSpeed] 44

Hi David

a formula can only affect the cell it is in, it can't move or change another 
cell for this you need some code ...

from what i gather you've got two lists from which you're picking random 
names, however, sometimes you get a duplicated name when you combine the 
results of the two lists and you don't want this to happen?  if this is the 
case, i don't understand how moving a name from g5 to j5 will help?

-- 
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" <DavidL@discussions.microsoft.com> wrote in message 
news:560BAC3E-698C-499F-8332-F9735DD20863@microsoft.com...
> Is there a way to move a cell contents to another cell with a formula. 
> ex:
> if a5="Name" then move g5 to j5?  Also, I am using
> =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a 
> list.
> I have the formula in different place pick random names from different 
> list.
> This does work, but I have different list with some of the same names and
> with the random pick I do not want the same name to appear.
> -- 
> Thanks for any and all help.
> Davidl 


0
JulieD1 (2295)
4/12/2005 4:42:01 PM
You cannot move with a formula, only refer to and then change to a value.
OR, you a macro.
range("a1").move range("b1")

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"David L" <DavidL@discussions.microsoft.com> wrote in message
news:560BAC3E-698C-499F-8332-F9735DD20863@microsoft.com...
> Is there a way to move a cell contents to another cell with a formula.
ex:
> if a5="Name" then move g5 to j5?  Also, I am using
> =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a
list.
> I have the formula in different place pick random names from different
list.
> This does work, but I have different list with some of the same names and
> with the random pick I do not want the same name to appear.
> -- 
> Thanks for any and all help.
> Davidl


0
Don
4/12/2005 4:43:30 PM
David

Formulas can return results but not "move" contents to other cells.

You would require a formula in J5 to show the results of G5.

Moving cell contents can be done through VBA code.


Gord Dibben Excel MVP

On Tue, 12 Apr 2005 09:37:02 -0700, "David L"
<DavidL@discussions.microsoft.com> wrote:

>Is there a way to move a cell contents to another cell with a formula.  ex: 
>if a5="Name" then move g5 to j5?  Also, I am using 
>=INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list.  
>I have the formula in different place pick random names from different list.  
>This does work, but I have different list with some of the same names and 
>with the random pick I do not want the same name to appear.

0
Gord
4/12/2005 4:55:28 PM
Hi Julie,
You are right about the duplicate names.  Actually, I have several list to 
pick from.  I have a front page.  It has 17 different locations I need names 
to go into.  Each list I randomly pick from has its own catogory.  The 
different catogories contain some of the same names.  I need to pick a random 
name from each catogory and not have them duplicate the same name within the 
17 locations.  If there is a vba code that can be written, I would appericate 
the help and instructions own how to use the code within the worksheet.

Thanks again,
David L

"JulieD" wrote:

> Hi David
> 
> a formula can only affect the cell it is in, it can't move or change another 
> cell for this you need some code ...
> 
> from what i gather you've got two lists from which you're picking random 
> names, however, sometimes you get a duplicated name when you combine the 
> results of the two lists and you don't want this to happen?  if this is the 
> case, i don't understand how moving a name from g5 to j5 will help?
> 
> -- 
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "David L" <DavidL@discussions.microsoft.com> wrote in message 
> news:560BAC3E-698C-499F-8332-F9735DD20863@microsoft.com...
> > Is there a way to move a cell contents to another cell with a formula. 
> > ex:
> > if a5="Name" then move g5 to j5?  Also, I am using
> > =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a 
> > list.
> > I have the formula in different place pick random names from different 
> > list.
> > This does work, but I have different list with some of the same names and
> > with the random pick I do not want the same name to appear.
> > -- 
> > Thanks for any and all help.
> > Davidl 
> 
> 
> 
0
DavidL1 (20)
4/12/2005 6:03:03 PM
Hi David

this could be difficult, as any code that i can envisage running would cause 
the sheet to recalculate - which means that it would change all of the 17 
names not just the duplicated ones (that's if all 17 of them are populated 
by a formula in the cell) .. a solution, therefore (from my knowledge base 
anyway) would be a macro that keeps running until all 17 locations have 
unique names in them, would that be an option .. however, basically, apart 
from the fact you have to check all 17 manually, you could achieve the same 
by pressing the F9 key ......... so, do you think a macro like this would be 
useful?
-- 
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" <DavidL@discussions.microsoft.com> wrote in message 
news:766D630E-D0B0-4806-83D6-608AA1183842@microsoft.com...
> Hi Julie,
> You are right about the duplicate names.  Actually, I have several list to
> pick from.  I have a front page.  It has 17 different locations I need 
> names
> to go into.  Each list I randomly pick from has its own catogory.  The
> different catogories contain some of the same names.  I need to pick a 
> random
> name from each catogory and not have them duplicate the same name within 
> the
> 17 locations.  If there is a vba code that can be written, I would 
> appericate
> the help and instructions own how to use the code within the worksheet.
>
> Thanks again,
> David L
>
> "JulieD" wrote:
>
>> Hi David
>>
>> a formula can only affect the cell it is in, it can't move or change 
>> another
>> cell for this you need some code ...
>>
>> from what i gather you've got two lists from which you're picking random
>> names, however, sometimes you get a duplicated name when you combine the
>> results of the two lists and you don't want this to happen?  if this is 
>> the
>> case, i don't understand how moving a name from g5 to j5 will help?
>>
>> -- 
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> news:560BAC3E-698C-499F-8332-F9735DD20863@microsoft.com...
>> > Is there a way to move a cell contents to another cell with a formula.
>> > ex:
>> > if a5="Name" then move g5 to j5?  Also, I am using
>> > =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a
>> > list.
>> > I have the formula in different place pick random names from different
>> > list.
>> > This does work, but I have different list with some of the same names 
>> > and
>> > with the random pick I do not want the same name to appear.
>> > -- 
>> > Thanks for any and all help.
>> > Davidl
>>
>>
>> 


0
JulieD1 (2295)
4/13/2005 3:20:04 PM
Hi David

okay here it is

first, select your 17 cells (use the control key to get them) and then click 
in the name box (little box to left of formula bar) and type
myrng
and press ENTER

then, right mouse click on a sheet tab and choose view code
in the VBE Window choose insert / module
on the right hand side of the screen copy & paste this
---

Sub eliminatedups()


    Dim Temp As Variant
    Dim i As Integer
    Dim NoExchanges As Integer
    Dim myvals(16) As String

here:
    Calculate
    i = 0
    For Each c In Range("myrng")
        myvals(i) = c.Value
        i = i + 1
    Next

    ' Loop until no more "exchanges" are made.
    Do
        NoExchanges = True

        ' Loop through each element in the array.
        For i = 0 To UBound(myvals) - 1
            ' If the element is greater than the element
            ' following it, exchange the two elements.
            If myvals(i) > myvals(i + 1) Then
                NoExchanges = False
                Temp = myvals(i)
                myvals(i) = myvals(i + 1)
                myvals(i + 1) = Temp
            ElseIf myvals(i) = myvals(i + 1) Then 'added GD
                GoTo here
            End If
        Next i
    Loop While Not (NoExchanges)

    msgbox "All OK"

End Sub

---

then use ALT & F11 to switch back to your workbook and create a button - 
assign the macro to the button and click it ....it should run through until 
there are no duplicates in the 17 cells.

let me know how you go.

-- 
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" <DavidL@discussions.microsoft.com> wrote in message 
news:ACA975D1-8229-4D10-A7F7-FC3F25934AB6@microsoft.com...
> Julie,
> I do believe that would help.  Can you give me an example of how to do 
> this?
> I can and the macro to a button to recaluclate the cells.
> Thanks,
> David
>
> "JulieD" wrote:
>
>> Hi David
>>
>> this could be difficult, as any code that i can envisage running would 
>> cause
>> the sheet to recalculate - which means that it would change all of the 17
>> names not just the duplicated ones (that's if all 17 of them are 
>> populated
>> by a formula in the cell) .. a solution, therefore (from my knowledge 
>> base
>> anyway) would be a macro that keeps running until all 17 locations have
>> unique names in them, would that be an option .. however, basically, 
>> apart
>> from the fact you have to check all 17 manually, you could achieve the 
>> same
>> by pressing the F9 key ......... so, do you think a macro like this would 
>> be
>> useful?
>> -- 
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> news:766D630E-D0B0-4806-83D6-608AA1183842@microsoft.com...
>> > Hi Julie,
>> > You are right about the duplicate names.  Actually, I have several list 
>> > to
>> > pick from.  I have a front page.  It has 17 different locations I need
>> > names
>> > to go into.  Each list I randomly pick from has its own catogory.  The
>> > different catogories contain some of the same names.  I need to pick a
>> > random
>> > name from each catogory and not have them duplicate the same name 
>> > within
>> > the
>> > 17 locations.  If there is a vba code that can be written, I would
>> > appericate
>> > the help and instructions own how to use the code within the worksheet.
>> >
>> > Thanks again,
>> > David L
>> >
>> > "JulieD" wrote:
>> >
>> >> Hi David
>> >>
>> >> a formula can only affect the cell it is in, it can't move or change
>> >> another
>> >> cell for this you need some code ...
>> >>
>> >> from what i gather you've got two lists from which you're picking 
>> >> random
>> >> names, however, sometimes you get a duplicated name when you combine 
>> >> the
>> >> results of the two lists and you don't want this to happen?  if this 
>> >> is
>> >> the
>> >> case, i don't understand how moving a name from g5 to j5 will help?
>> >>
>> >> -- 
>> >> Cheers
>> >> JulieD
>> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> ....well i'm working on it anyway
>> >> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> >> news:560BAC3E-698C-499F-8332-F9735DD20863@microsoft.com...
>> >> > Is there a way to move a cell contents to another cell with a 
>> >> > formula.
>> >> > ex:
>> >> > if a5="Name" then move g5 to j5?  Also, I am using
>> >> > =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from 
>> >> > a
>> >> > list.
>> >> > I have the formula in different place pick random names from 
>> >> > different
>> >> > list.
>> >> > This does work, but I have different list with some of the same 
>> >> > names
>> >> > and
>> >> > with the random pick I do not want the same name to appear.
>> >> > -- 
>> >> > Thanks for any and all help.
>> >> > Davidl
>> >>
>> >>
>> >>
>>
>>
>> 


0
JulieD1 (2295)
4/13/2005 4:48:43 PM
Hi David

did you delete & recreate "myrng" every time you tested a different number 
of cells?

-- 
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" <DavidL@discussions.microsoft.com> wrote in message 
news:D3629582-5E17-409F-8732-8FCDCE138512@microsoft.com...
> Hi Julie,
>
> Thank you for your help.  I had told you wrong about the number of cells I
> have to check for duplicates.  Instead of 17 there are 20.  I change the
> macro to read Dim myvals(19) As String.  I am not sure if this is right. 
> I
> can change the range to have 18 cells and change the macro to Dim 
> myvals(17)
> As String and the macro runs and works and it takes less than 30 seconds.
> With the 20 cells I just had to cancel the macro after it had been running
> for 15 minutes.  I was wondering if there is something else I need to 
> change
> to make it run faster.
>
> Thanks,
> David L
>
> "JulieD" wrote:
>
>> Hi David
>>
>> okay here it is
>>
>> first, select your 17 cells (use the control key to get them) and then 
>> click
>> in the name box (little box to left of formula bar) and type
>> myrng
>> and press ENTER
>>
>> then, right mouse click on a sheet tab and choose view code
>> in the VBE Window choose insert / module
>> on the right hand side of the screen copy & paste this
>> ---
>>
>> Sub eliminatedups()
>>
>>
>>     Dim Temp As Variant
>>     Dim i As Integer
>>     Dim NoExchanges As Integer
>>     Dim myvals(16) As String
>>
>> here:
>>     Calculate
>>     i = 0
>>     For Each c In Range("myrng")
>>         myvals(i) = c.Value
>>         i = i + 1
>>     Next
>>
>>     ' Loop until no more "exchanges" are made.
>>     Do
>>         NoExchanges = True
>>
>>         ' Loop through each element in the array.
>>         For i = 0 To UBound(myvals) - 1
>>             ' If the element is greater than the element
>>             ' following it, exchange the two elements.
>>             If myvals(i) > myvals(i + 1) Then
>>                 NoExchanges = False
>>                 Temp = myvals(i)
>>                 myvals(i) = myvals(i + 1)
>>                 myvals(i + 1) = Temp
>>             ElseIf myvals(i) = myvals(i + 1) Then 'added GD
>>                 GoTo here
>>             End If
>>         Next i
>>     Loop While Not (NoExchanges)
>>
>>     msgbox "All OK"
>>
>> End Sub
>>
>> ---
>>
>> then use ALT & F11 to switch back to your workbook and create a button -
>> assign the macro to the button and click it ....it should run through 
>> until
>> there are no duplicates in the 17 cells.
>>
>> let me know how you go.
>>
>> -- 
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> news:ACA975D1-8229-4D10-A7F7-FC3F25934AB6@microsoft.com...
>> > Julie,
>> > I do believe that would help.  Can you give me an example of how to do
>> > this?
>> > I can and the macro to a button to recaluclate the cells.
>> > Thanks,
>> > David
>> >
>> > "JulieD" wrote:
>> >
>> >> Hi David
>> >>
>> >> this could be difficult, as any code that i can envisage running would
>> >> cause
>> >> the sheet to recalculate - which means that it would change all of the 
>> >> 17
>> >> names not just the duplicated ones (that's if all 17 of them are
>> >> populated
>> >> by a formula in the cell) .. a solution, therefore (from my knowledge
>> >> base
>> >> anyway) would be a macro that keeps running until all 17 locations 
>> >> have
>> >> unique names in them, would that be an option .. however, basically,
>> >> apart
>> >> from the fact you have to check all 17 manually, you could achieve the
>> >> same
>> >> by pressing the F9 key ......... so, do you think a macro like this 
>> >> would
>> >> be
>> >> useful?
>> >> -- 
>> >> Cheers
>> >> JulieD
>> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> ....well i'm working on it anyway
>> >> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> >> news:766D630E-D0B0-4806-83D6-608AA1183842@microsoft.com...
>> >> > Hi Julie,
>> >> > You are right about the duplicate names.  Actually, I have several 
>> >> > list
>> >> > to
>> >> > pick from.  I have a front page.  It has 17 different locations I 
>> >> > need
>> >> > names
>> >> > to go into.  Each list I randomly pick from has its own catogory. 
>> >> > The
>> >> > different catogories contain some of the same names.  I need to pick 
>> >> > a
>> >> > random
>> >> > name from each catogory and not have them duplicate the same name
>> >> > within
>> >> > the
>> >> > 17 locations.  If there is a vba code that can be written, I would
>> >> > appericate
>> >> > the help and instructions own how to use the code within the 
>> >> > worksheet.
>> >> >
>> >> > Thanks again,
>> >> > David L
>> >> >
>> >> > "JulieD" wrote:
>> >> >
>> >> >> Hi David
>> >> >>
>> >> >> a formula can only affect the cell it is in, it can't move or 
>> >> >> change
>> >> >> another
>> >> >> cell for this you need some code ...
>> >> >>
>> >> >> from what i gather you've got two lists from which you're picking
>> >> >> random
>> >> >> names, however, sometimes you get a duplicated name when you 
>> >> >> combine
>> >> >> the
>> >> >> results of the two lists and you don't want this to happen?  if 
>> >> >> this
>> >> >> is
>> >> >> the
>> >> >> case, i don't understand how moving a name from g5 to j5 will help?
>> >> >>
>> >> >> -- 
>> >> >> Cheers
>> >> >> JulieD
>> >> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> >> ....well i'm working on it anyway
>> >> >> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> >> >> news:560BAC3E-698C-499F-8332-F9735DD20863@microsoft.com...
>> >> >> > Is there a way to move a cell contents to another cell with a
>> >> >> > formula.
>> >> >> > ex:
>> >> >> > if a5="Name" then move g5 to j5?  Also, I am using
>> >> >> > =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names 
>> >> >> > from
>> >> >> > a
>> >> >> > list.
>> >> >> > I have the formula in different place pick random names from
>> >> >> > different
>> >> >> > list.
>> >> >> > This does work, but I have different list with some of the same
>> >> >> > names
>> >> >> > and
>> >> >> > with the random pick I do not want the same name to appear.
>> >> >> > -- 
>> >> >> > Thanks for any and all help.
>> >> >> > Davidl
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
JulieD1 (2295)
4/14/2005 3:39:57 PM
Hi David

you did the right thing by changing the Dim myvals(x) As String and by 
deleting and recreating the myrng range name before running it for a 
different number of cells ... but ...

the way the code works is that it checks the 20 (or whatever cells) and if 
it finds a duplicate it recalcs all the cells and checks them again ... so 
unfortunately it seems that it is taking a long time to generate 20 cells 
without duplication.

I can't think of any way of speeding it up ... unless you only include in 
the myrng range name those fields where duplications are possible, rather 
than all 20 (if this is an option), then it will have less values to check 
and compare.

if this doesn't work, then AFAIK the sitauation needs to be approach 
differently but i'm not sure how.

-- 
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David L" <DavidL@discussions.microsoft.com> wrote in message 
news:BA03CC56-1DD8-43DE-9C4B-08D6EBEB970C@microsoft.com...
> Julie,
>    I redefined the myrng range to the number of cells I was trying.  When 
> I
> redefined the range and changed the number I worked, but when I went to 20
> cells in the myrng range and changed the number to 19 that is when it is
> taking so long.
>
> Thanks,
> David L
>
> "JulieD" wrote:
>
>> Hi David
>>
>> did you delete & recreate "myrng" every time you tested a different 
>> number
>> of cells?
>>
>> -- 
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> news:D3629582-5E17-409F-8732-8FCDCE138512@microsoft.com...
>> > Hi Julie,
>> >
>> > Thank you for your help.  I had told you wrong about the number of 
>> > cells I
>> > have to check for duplicates.  Instead of 17 there are 20.  I change 
>> > the
>> > macro to read Dim myvals(19) As String.  I am not sure if this is 
>> > right.
>> > I
>> > can change the range to have 18 cells and change the macro to Dim
>> > myvals(17)
>> > As String and the macro runs and works and it takes less than 30 
>> > seconds.
>> > With the 20 cells I just had to cancel the macro after it had been 
>> > running
>> > for 15 minutes.  I was wondering if there is something else I need to
>> > change
>> > to make it run faster.
>> >
>> > Thanks,
>> > David L
>> >
>> > "JulieD" wrote:
>> >
>> >> Hi David
>> >>
>> >> okay here it is
>> >>
>> >> first, select your 17 cells (use the control key to get them) and then
>> >> click
>> >> in the name box (little box to left of formula bar) and type
>> >> myrng
>> >> and press ENTER
>> >>
>> >> then, right mouse click on a sheet tab and choose view code
>> >> in the VBE Window choose insert / module
>> >> on the right hand side of the screen copy & paste this
>> >> ---
>> >>
>> >> Sub eliminatedups()
>> >>
>> >>
>> >>     Dim Temp As Variant
>> >>     Dim i As Integer
>> >>     Dim NoExchanges As Integer
>> >>     Dim myvals(16) As String
>> >>
>> >> here:
>> >>     Calculate
>> >>     i = 0
>> >>     For Each c In Range("myrng")
>> >>         myvals(i) = c.Value
>> >>         i = i + 1
>> >>     Next
>> >>
>> >>     ' Loop until no more "exchanges" are made.
>> >>     Do
>> >>         NoExchanges = True
>> >>
>> >>         ' Loop through each element in the array.
>> >>         For i = 0 To UBound(myvals) - 1
>> >>             ' If the element is greater than the element
>> >>             ' following it, exchange the two elements.
>> >>             If myvals(i) > myvals(i + 1) Then
>> >>                 NoExchanges = False
>> >>                 Temp = myvals(i)
>> >>                 myvals(i) = myvals(i + 1)
>> >>                 myvals(i + 1) = Temp
>> >>             ElseIf myvals(i) = myvals(i + 1) Then 'added GD
>> >>                 GoTo here
>> >>             End If
>> >>         Next i
>> >>     Loop While Not (NoExchanges)
>> >>
>> >>     msgbox "All OK"
>> >>
>> >> End Sub
>> >>
>> >> ---
>> >>
>> >> then use ALT & F11 to switch back to your workbook and create a 
>> >> button -
>> >> assign the macro to the button and click it ....it should run through
>> >> until
>> >> there are no duplicates in the 17 cells.
>> >>
>> >> let me know how you go.
>> >>
>> >> -- 
>> >> Cheers
>> >> JulieD
>> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> ....well i'm working on it anyway
>> >> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> >> news:ACA975D1-8229-4D10-A7F7-FC3F25934AB6@microsoft.com...
>> >> > Julie,
>> >> > I do believe that would help.  Can you give me an example of how to 
>> >> > do
>> >> > this?
>> >> > I can and the macro to a button to recaluclate the cells.
>> >> > Thanks,
>> >> > David
>> >> >
>> >> > "JulieD" wrote:
>> >> >
>> >> >> Hi David
>> >> >>
>> >> >> this could be difficult, as any code that i can envisage running 
>> >> >> would
>> >> >> cause
>> >> >> the sheet to recalculate - which means that it would change all of 
>> >> >> the
>> >> >> 17
>> >> >> names not just the duplicated ones (that's if all 17 of them are
>> >> >> populated
>> >> >> by a formula in the cell) .. a solution, therefore (from my 
>> >> >> knowledge
>> >> >> base
>> >> >> anyway) would be a macro that keeps running until all 17 locations
>> >> >> have
>> >> >> unique names in them, would that be an option .. however, 
>> >> >> basically,
>> >> >> apart
>> >> >> from the fact you have to check all 17 manually, you could achieve 
>> >> >> the
>> >> >> same
>> >> >> by pressing the F9 key ......... so, do you think a macro like this
>> >> >> would
>> >> >> be
>> >> >> useful?
>> >> >> -- 
>> >> >> Cheers
>> >> >> JulieD
>> >> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> >> ....well i'm working on it anyway
>> >> >> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> >> >> news:766D630E-D0B0-4806-83D6-608AA1183842@microsoft.com...
>> >> >> > Hi Julie,
>> >> >> > You are right about the duplicate names.  Actually, I have 
>> >> >> > several
>> >> >> > list
>> >> >> > to
>> >> >> > pick from.  I have a front page.  It has 17 different locations I
>> >> >> > need
>> >> >> > names
>> >> >> > to go into.  Each list I randomly pick from has its own catogory.
>> >> >> > The
>> >> >> > different catogories contain some of the same names.  I need to 
>> >> >> > pick
>> >> >> > a
>> >> >> > random
>> >> >> > name from each catogory and not have them duplicate the same name
>> >> >> > within
>> >> >> > the
>> >> >> > 17 locations.  If there is a vba code that can be written, I 
>> >> >> > would
>> >> >> > appericate
>> >> >> > the help and instructions own how to use the code within the
>> >> >> > worksheet.
>> >> >> >
>> >> >> > Thanks again,
>> >> >> > David L
>> >> >> >
>> >> >> > "JulieD" wrote:
>> >> >> >
>> >> >> >> Hi David
>> >> >> >>
>> >> >> >> a formula can only affect the cell it is in, it can't move or
>> >> >> >> change
>> >> >> >> another
>> >> >> >> cell for this you need some code ...
>> >> >> >>
>> >> >> >> from what i gather you've got two lists from which you're 
>> >> >> >> picking
>> >> >> >> random
>> >> >> >> names, however, sometimes you get a duplicated name when you
>> >> >> >> combine
>> >> >> >> the
>> >> >> >> results of the two lists and you don't want this to happen?  if
>> >> >> >> this
>> >> >> >> is
>> >> >> >> the
>> >> >> >> case, i don't understand how moving a name from g5 to j5 will 
>> >> >> >> help?
>> >> >> >>
>> >> >> >> -- 
>> >> >> >> Cheers
>> >> >> >> JulieD
>> >> >> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> >> >> ....well i'm working on it anyway
>> >> >> >> "David L" <DavidL@discussions.microsoft.com> wrote in message
>> >> >> >> news:560BAC3E-698C-499F-8332-F9735DD20863@microsoft.com...
>> >> >> >> > Is there a way to move a cell contents to another cell with a
>> >> >> >> > formula.
>> >> >> >> > ex:
>> >> >> >> > if a5="Name" then move g5 to j5?  Also, I am using
>> >> >> >> > =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random 
>> >> >> >> > names
>> >> >> >> > from
>> >> >> >> > a
>> >> >> >> > list.
>> >> >> >> > I have the formula in different place pick random names from
>> >> >> >> > different
>> >> >> >> > list.
>> >> >> >> > This does work, but I have different list with some of the 
>> >> >> >> > same
>> >> >> >> > names
>> >> >> >> > and
>> >> >> >> > with the random pick I do not want the same name to appear.
>> >> >> >> > -- 
>> >> >> >> > Thanks for any and all help.
>> >> >> >> > Davidl
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
JulieD1 (2295)
4/14/2005 4:07:12 PM
noteing your answer. Is there a way to copy or save results of formula to 
another location and clear the results in the active workbook? Also need to 
retain formulas in active. Would formulas need to be reinserted by code?
Thanks 
cagreer@att.net
Curt

"Gord Dibben" wrote:

> David
> 
> Formulas can return results but not "move" contents to other cells.
> 
> You would require a formula in J5 to show the results of G5.
> 
> Moving cell contents can be done through VBA code.
> 
> 
> Gord Dibben Excel MVP
> 
> On Tue, 12 Apr 2005 09:37:02 -0700, "David L"
> <DavidL@discussions.microsoft.com> wrote:
> 
> >Is there a way to move a cell contents to another cell with a formula.  ex: 
> >if a5="Name" then move g5 to j5?  Also, I am using 
> >=INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list.  
> >I have the formula in different place pick random names from different list.  
> >This does work, but I have different list with some of the same names and 
> >with the random pick I do not want the same name to appear.
> 
> 
0
Curt (33)
11/24/2005 7:48:02 PM
Curt

You can copy the results of formulas from a sheet to another sheet within the
same workbook or to another workbook and paste special as values.

Hit CTRL + a to select all cells.

F5>Special>Formulas>OK

With those cells cells selected hit Edit>Copy then Paste Special>Values to
wherever you want.

To retain formulas in source worksheet CTRL + a to select all cells then hit
F5>Special>Constants.  Check which Constants to delete and OK

With those selected cells Edit>Clear Contents.

Formulas will remain ready for input of new data where the constants were
deleted.


Gord Dibben Excel MVP

On Thu, 24 Nov 2005 11:48:02 -0800, "Curt" <Curt@discussions.microsoft.com>
wrote:

>noteing your answer. Is there a way to copy or save results of formula to 
>another location and clear the results in the active workbook? Also need to 
>retain formulas in active. Would formulas need to be reinserted by code?
>Thanks 
>cagreer@att.net
>Curt
>
>"Gord Dibben" wrote:
>
>> David
>> 
>> Formulas can return results but not "move" contents to other cells.
>> 
>> You would require a formula in J5 to show the results of G5.
>> 
>> Moving cell contents can be done through VBA code.
>> 
>> 
>> Gord Dibben Excel MVP
>> 
>> On Tue, 12 Apr 2005 09:37:02 -0700, "David L"
>> <DavidL@discussions.microsoft.com> wrote:
>> 
>> >Is there a way to move a cell contents to another cell with a formula.  ex: 
>> >if a5="Name" then move g5 to j5?  Also, I am using 
>> >=INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list.  
>> >I have the formula in different place pick random names from different list.  
>> >This does work, but I have different list with some of the same names and 
>> >with the random pick I do not want the same name to appear.
>> 
>> 

0
Gord
11/24/2005 8:18:17 PM
Reply:

Similar Artilces:

format cells #6
I have solved this so it is just my curiosity at play here. Using someone else's sheet, I entered a formula (=D235/80) into a cell expecting a numeric result not realizing that the cell was formatted as Text (so I got to see the formula as text). So I changed the cell format to Number with 4 places. To my surprise, the cell still displayed the formula. I looked for a leading ' or space but there was none. Retyping the formula produced the result I expected in the first place. What is going on? TIA Len ______________________________________________________ Changing the forma...

Counting Cells #3
I want to be able to count the number of cells that have coloured text from a range of cells. Is their a way to do this, I tried "countif" but was unable to get it working. Thanks See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Missile" <Missile@discussions.microsoft.com> wrote in message news:A3FCE1A8-D5FE-49CD-B7B8-8B08C493F9F2@microsoft.com... > I want to be able to count the number of cells that have coloured text from a > range of cells. Is their ...

Eliminating cells with a zero
I have a matrix of data that lists part numbers in row [1] and serial numbers in column [A] as shown in Example 1. In the example below (Example 1), the range beginning in cell [B2] and ending in cell [H6], is the number of days from that serial number's manufacturing date that a part was replaced; i.e. Serial # 12A had PART2 replaced 528 days after 12A's manufacture. Leaving the data in Example 1 intact, I need to create another matrix to summarize the data as displayed in Example 2. Where per serial number, the parts that have a "0" are eliminated, and I...

Duplicate cells #4
I have an excel spreadsheet of approximately 1500 Names,addressses etc. there are many duplicate cells of which I want to delete the duplicates and I am looking for the best way to accomplish this. I am new to excel and using formulas but if there is a way to use color to point out the duplicates, that would be very helpfull, thanks You could try to use Advanced Filter to get rid of the duplicates. Ensure that you have headers in row 1 (Name, Address_1, Address_2 etc) and highlight all the data including the headers. Then click Data | Filter | Advanced Filter and in the pop up click Un...

Relating One cell Value to another to the left of it.
Below is an extract from an excel spreadsheet I am currently working on. There are three columns, one with standard hours (Stnd Hrs.) one with Overtime hours (Ovtm Hrs.) and one with an Overtime Code (Ovtm Code). Sunday 03-Aug-03 Stnd Hrs. Ovtm Hrs. Ovtm Code 7 2 A 7 3 B 5 3 C 5 3 C 3 6 F 4 2 D 5 6 B 4 3 A 0 ...

Inserting carriage returns in Excel cells
How do you insert a carriage return within an Excel cell? Alt + Enter -- Regards, Peo Sjoblom "Alyce" <Alyce@discussions.microsoft.com> wrote in message news:67A686A5-BF3F-4AE0-A404-A58943DBEEDB@microsoft.com... > How do you insert a carriage return within an Excel cell? I knew there must be a very simple way of doing it. Thanks so much. "Peo Sjoblom" wrote: > Alt + Enter > > -- > > Regards, > > Peo Sjoblom > > > "Alyce" <Alyce@discussions.microsoft.com> wrote in message > news:67A686A5-BF3F-4AE0-A...

getting cell values from multible sheets
I have 100+ sheets in a workbook that I want to get a cell value (or defined cell name) from each sheet and place them in one sheet. So, i there was a cell named count1 for sheet one and count2 for sheet2 etc I need to collect those names and place them on one sheet ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Insert, Name, Paste, Paste list should do the trick. Hope I understood your question properly. Gilles "bwcalvin" <bwcalvin.xizpy@excelforum-nospam...

Clearing Cells
I would like to clear the data (not the formatting) in cells in a workbook or worksheet that have a conditional format but is not a formula. There are about 30 ranges in each sheet and 31 sheets to the workbook. I know how to clear the contents of a range of cells but do not know what to do to find conditional formatting without formulas. I would like to do it to all sheets beginning with sheet "01" and ending with sheet "31". -- Thank You in Advance Ed Davis Which version are you running? If 2003 or earlier you can group the sheets and F5>Special>Condit...

cell reference #7
Hi if cell A1 is reference to A10. And when i double click in cell A1 it goes to cell A10. my question is how to come back to cell A1. Try this. If it is 2003: Tools>>Options>>Edit>>Settings>>(Check the) Edit Directly in Cell If it is 2007: Excel Options>>Editing Options>>(Check the) Allow Editing Directly in Cells Now go to a1 cell and do double click If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "Rohit" wrote: > Hi > > if cell A1 is reference to A10. And when i double click in cell...

REMOVE ALL THE BORDERS AROUND A CELL
how do I remove the border around a cell? thank you. right-click the cell, then click Format Cells. click the Border tab and you can remove the borders by clicking on them. or to remove all of them, click the preset None. "Raymond" wrote: > how do I remove the border around a cell? > > thank you. > > > Thank you stefno. You are pointing me to the right place and direction. However, I could not remove the border after selected the preset none. I chose the none for the light too from the list of lines on the right but after clicked ok and got back to the...

Can Mailboxes be moved to different location?
Is it possible to move user's mailboxes to another drive location on the same server as the Exchange program or another (Network Attached Storage) location? check this out: http://support.microsoft.com/?id=257184 Another drive on the same box? Sure. Not a NAS, tho - a SAN is supported, but not NAS or a mapped network drive of any sort. Jeff Catalucci wrote: > Is it possible to move user's mailboxes to another drive > location on the same server as the Exchange program or > another (Network Attached Storage) location? Using Exchange Server with Storage-Attached Network...

MRP Purchase Order Move Ins
I have created a smartlist, that will list the purchase order move Ins, (MPW05000 table) However our buyers would like to see the purchase order receipt date also to compare (sometimes the date is only 1 day move in which we do not want to take the time to change) Has anyone been able to link the MRP moveins and the purchase order lines? thanks! -- Doug ...

moving 450 active e-mail addresses from Excel to Outlook Express
i want to copy 450 E-mail addresses(active) from Excel to Outlook Express where they will be used to form various Groups. How do I copy/move these addresses? Hi AFAIK Outlook can import *.csv files -- Regards Frank Kabel Frankfurt, Germany "ragman10" <ragman10@discussions.microsoft.com> schrieb im Newsbeitrag news:EE7E24D0-B7EE-4741-8B2F-D00223A3916C@microsoft.com... > i want to copy 450 E-mail addresses(active) from Excel to Outlook Express > where they will be used to form various Groups. How do I copy/move these > addresses? ...

unprotecting selected cells
I want my employees to be able to enter data into some cells in a worksheet but the rest I want protected. I go to protection under tools and protect the worksheet but can't figure out how to unlock the few I want accessible. Glenn I'd mark all of the cells ctrl-a, press ctrl-1 => protection => lock. Then mark only those cells you'd want others to edit, and ctrl-1 => protection => unlock. then you protect the sheet. good luck -- ____________________ Peter Andersen account_name@hotmail.com ____________________ "Glenn Rowe" <extremepyro@comcast.net&g...

Copy from row above if cell is empty in column
How can I get a column to test if there is text in a row above when there is an empty cell, and then copy the text from the row above into the empty cell automaticlly? Thanks, Jacky D. =if(a1="","",a1) (in A2) If you're trying to fill a bunch of cells with the value from the above cells, you may want to take a look at Debra Dalgleish's site: http://contextures.com/xlDataEntry02.html She has some manual ways (very quick though) and some code, too. Jacky D. wrote: > > How can I get a column to test if there is text in a row above when there is > an ...

Windows resize small and stay stuck when moving from external monitor to laptop only
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, if I have a window open in word on my external monitor, then unplug my laptop, when I open up my laptop again, the word window is shrunk to a 1/4 of the size and it can't be dragged/resized larger. I have to quit and restart word. How can I fix this? Click the little Display icaon at the top of your screen. Adjust the resolution smaller, then adjust it back to where you want it. Then it allows you to drag/resize. Marty Koenig CxO To Go TonyD@officeformac.com wrote: >Version: 2008 >Operating System: Mac ...

Excel Cell + Image
Hi, I am looking for option of inserting image to excel cell. problem is i have more then 25000 images, manually inserting will take year to do So need solution that works fast...and can show all images at referenced cell. so we can filter images. suppose image name is "apple" in one colum.. then image should display apple "banana" then it should show banana....we can do this with filter option of excel but only if..we able to insert the images..in cell i don't know how to do it quickly. -- smd111 ---------------------------------------------------------------...

double click a cell and add 1 to that cess
I need code to add 1 to a tally sheet in certain cells. The sheet has a bunch of differenct cells that are seperate. Meaning a range of A1:F18 and then a different range of A55:G81. There are about 4 different ranges or group of cells that I need the user to be able to doubleclick if they wish to add 1 to the current value in that cell. Any help is appreciated. Right click the worksheet tab and choose View Code. In the code module that is displayed, paste the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim Rng As Range S...

Format Text in Date Cell
I am making a type of yearly calendar in Excel and I want to have certain cells that contain certain days of the week to have different colors of text. Sunday=Red and Friday=Blue An example of how the cells I am working on is: Friday, January 01, 2010 Saturday, January 02, 2010 Sunday, January 03, 2010 Monday, January 04, 2010 Tuesday, January 05, 2010 Wednesday, January 06, 2010 Thursday, January 07, 2010 Friday, January 08, 2010 Saturday, January 09, 2010 Sunday, January 10, 2010 Monday, January 11, 2010 Tuesday, January 12, 2010 Wednesday, January 13, 2010 Thursday, Janua...

How do I get excel to "count" cells along a row?
I hope I can make this make sense. I am sure the answer is very simple as every spreadsheet that underpins a budget must allow for flexible forecasting. I am trying to create a spreadsheet that allows me to start calculations anywhere on a time line and allows me to change the amount of time that the calculations will last. For example, if I am looking at a 12 month time line (Jan to Dec) then I want to be able to start a function in any month and then for that function to last for any amount of time. So the first function may start in Jan and last for three months. In parallel a se...

Reference a cell in criteria range
I want to average a range if 2 criteria are met; the criteria includes cell references: C6 = 1/1/2009 (internal date) E6 = 1/1/2010 (internal date) Cells B11:BJ11 contain valid dates Cells C11:AJ11 contain valid numbers I want to average the values in C11:AJ11 that are >= C6 and <= E6. Can I reference the cells in the criteria range? I used averageifs(C11:AJ11,B11:BJ11,">=C6",B11:BJ11,"<=E6") which of course did not work. Thanks! Hi Connie You are very close: =averageifs(C11:AJ11,B11:BJ11,">=" & C6...

Splitting a cell, with a twist!
Hi, I have data (names) which appear with Surname first, with all other names after that. (all in one cell). For example: Earles Prof Stanley William Edward What I want to do, is split this so that the first word (in this case "Earles" stays, and the rest is shifted into a new column. Any ideas? PS...I cant get Data, text to columns to work! Data, Text to Columns, Tick delimited, <Next> Select "Space" as delimiter, <Next>, leave format as General, <Finish> and it will split this entry into 5 columns "christinab" wrote: > Hi, > ...

How to Highlight Formula Cells
Would some kind soul please tell me how to make spreadsheets highlight cells which contain formulas (to ensure I am very careful when making changes)? (Excel 2003). If you do a Ctrl-` that will show the formula in the cell rather than the results of the formula. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "blank" <blank@void.net> wrote in message news:12evnvhd30c55d4@corp.supernews.com... > Would some kind soul please tell me how to make spreadsheets highlight cells > which contain formulas (to ensure I am very caref...

Insert formula and copy to a range of cells above
Can anyone help. I have some data being output to an excel worksheet and the number of rows output is never the same. I have written some code to find the last row used and then select the cell in this row relating to a certain column. I am now having trouble insetring a formula into this cell and copying this formula to all the cells above in the same column. a sample worksheet would look like this: Tag num Jig Num Wgt A Wgt B Wgt Diff 1 100 90 95 2 101 85 95 6 130 110 ...

How do I set specific cells for mandatory entry?
I want to set a group of cells which cannot be left blank in a worksheet. is this possible? Possibly. What are your preventing? (what process would occur that you don't want to happen if there are blanks) One simple way is: =COUNTBLANK(A2,B2,C3:C6)=0 Where you refer to all the cells you don't want blank. Formula returns a boolean, which can be used in various ways to stop/halt/notify people. You might also look into using the formula in Data-Validation. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "vanarsdalet&...