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
535 Views

Similar Articles

[PageSpeed] 4

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:

Testing a range of cells in an IF FUNCTION
Trying to test a range of cells in an IF Function. I would like the function to look at 15 cells in a single row, find a value within those cells, and then return another value if TRUE/FALSE. As of now, I can only apply the "logical test" in the IF formula for ONE CELL ONLY. I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then return a value. Anyone understand or know how to do this? I tried apply "lookup" function, but I don't think it will work Please help Aaro aaronplange at hotmail.com Hi maybe someth...

Moving Mailboxes.
When moving mailboxes between 2 servers using the "move mailbox" command, is there a way to compress or zip up a large mailbox so that it does not take so long to move? Any help would be greatly appreciated. Roger Settle RSettle@klfinancialgroup.com "Roger Settle" <rsettle@klfinancialgroupREMOVETHIS.com> wrote: >When moving mailboxes between 2 servers using the "move >mailbox" command, is there a way to compress or zip up a >large mailbox so that it does not take so long to move? >Any help would be greatly appreciated. > >Roger S...

Trying to move email from inbox to personal folder
In Outlook 2007, I am trying to move e-mails from my inbox to a personal folder. I get the error message, �Can�t move the items. The items could not be moved. It was either already moved or deleted, or access was denied.� I have done this successfully before. Any suggestions? -- Stevevh1 "Stevevh1" <Stevevh1.42ab33e@outlookbanter.com> wrote in message news:Stevevh1.42ab33e@outlookbanter.com... > In Outlook 2007, I am trying to move e-mails from my inbox to a personal > folder. I get the error message, Cant move the items. The items could > not be moved. It w...

assign cell content as file name
I am attempting to create a macro that will save a workbook with the file name under which I save the workbook coming from the contents of one of the cells in the workbook. Suppose that each time the workbook was altered, a specific cell (A1 on Sheet1 for instance) contained the desired file name for that workbook. It is my goal to create a macro that automatically assigns said workbook a name based on the content of that cell. Any help would be most appreciated. Something like??? dim myFileName as string myfilename = "C:\temp\" & thisworkbook.worksheets("sheet1"...

The Item could not be deleted. It was either moved or already deleted, or access was denied.
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C42C5B.11C3D7B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I had to rebuild a corrupt database this morning. My Priv.edb and = Pub.edb were the files I worked on with the ESEUTIL. Everything is = working except users are complaining that they cannot delete e-mails = that were "zapped" during the rebuild process. The message they're = receiving is " The Item could not be deleted. It was either moved or = already deleted, or access was denied...

spell check does not visually cycle through cells when run from macro?
I'm trying to recreate the same behavior in a macro that spellcheck has when the spellcheck button is pressed. The spellcheck does not cycle through the cells visually when run from a macro, so when there is a misspelled word, you cannot see the cell it is a part of. If anyone knows how I can get this behavior to work, it would be GREATLY appreciated. I did a record new macro, and got code similar to the following: Sub testSpelling() Range("F2:F500").Select Selection.CheckSpelling SpellLang:=1033 End Sub thanks, dan Dan, Add the fiollowing to your code as shown: ...

Formatting cells from internet
I have copied and pasted a chart from a website. (The website will not let me import) The cells are showing numbered values, but when i go to put the formula in it is giving me a zero like it cannot read the numbers in the cell. That is on 3 seperate columns. On the very last column it is reading the numbers I cannot make it recognize that those columns are numbers to be used in formulas Thanks for your help! Use Dave McRitchie's Trimall macro to clean up the data. Then copy an empty cell, select all your data and do Edit / PasteSpecial / Add and it will coerce all data back to nu...

displyaing the referenced cells ?
Hi everyone ! Working on an excel file full of references, it would be very useful to be able to make the display to jump directly to the cells referenced by the active cells I currently see. So if my cell TU789 reference the cell JI890, I don't want to search for JI 890, but would like excel to automatically show me this cell. Do you know if it is possible ? Is there a shortcut for that ? Thanks, JR. It would be difficult to do that generically, as the cell could contain a formula that references several other cells - which one should be chosen in this case? You might think about using ...

can lookup return cell reference istead of "text" for sumif?
I am trying to use a lookup-function to determine a different sum range for several criteria. Like so: =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;false)-Sumif($A$7:$A $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false) The problem is that the vlookup returns text and not the cell reference. Is there a way to get the answer from the lookup expressed as cell reference instead of text, since sumif can't use text, just the cell reference? I use it to calculate the number of hours the staff should be paid, so it's different from weekdays to saturdays, holidays...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

How do I get automatic completion to work for an in-cell dropdown
If I have a list of Alpha, Beta, Gamma and an in-cell dropdown, I'd like typing 'G' to auto-select Gamma. I'm pretty sure it was doing that at some point, but now, it does not. This isn't supported under data|validation. You could use A,B,G in the dropdown and then use a helper cell with a formula in it, though. Say you put your abbreviations in A1:A3 of sheet2 and the real names in B1:B3, then you could use: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) Joseph Weisblatt wrote: > > If I have a list of Alpha, Beta, Gamma and an in-cell dropd...

Highlighting A Cell
Does anyone know how to do a formula that says if a cell equals the wor FALSE then highlight this particular cell gray? Example: Typing in Cell A2 IF A1 = FALSE then gray otherwise white I tried to do it in Conditioning Formatting but can't figure it out t reference another cell. Any help would be greatly appreciated -- Coltsfa ----------------------------------------------------------------------- Coltsfan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1998 View this thread: http://www.excelforum.com/showthread.php?threadid=46898 Good evening Colt...

Make a cell flash
Please help I to make cell b1 flash when it becomes greater than 5 See: http://www.cpearson.com/excel/BlinkingText.htm -- Gary's Student "Lloyd" wrote: > Please help I to make cell b1 flash when it becomes greater than 5 see response in excel.misc -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Lloyd" <Lloyd@discussions.microsoft.com> wrote in message news:08B5FB85-6AAB-4B7E-ABC1-8CC8E2B61466@microsoft.com... > Please help I to make cell b1 flash when it becomes greater than 5 Thanks for the link. I ...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

Averaging cell's...problems with Div/0
Hi guys. First time poster here so be gentle with me. I am looking fo some assistance averaging a range of 1 to 3 numbers. Here is what I have so far. =(D3+F3+H3)/(IF(C3=0,0,1)+IF(E3=0,0,1)+IF(G3=0,0,1)) This works great. What it does is checks to see if there is a value i the cell, then counts it and divides by the right number. I.E if yo only have two values out of 3 filled in it divides the number by tw instead of 3. My problem... if all 3 fields are 0 then I get a divide by 0 error. Any suggestion on how to fix this? I don't want my spreadsheet to loo messy before I start plu...

How can I autofill a series to reference non adjacent cells?
I need to create a list of values by referencing a series of non adjacent cells (they are separated by a set number of rows each but in the same column). For example the cells should read something like =b2 =b4 =b6 =b8. Of course I could type these in individually but threre are something like 100 values. Is there a way to auto fill this? Look at the indirect function for your example =indirect("B"&(row()*2)) copied down a column will give you a list for non adjacencies. "Microcell" wrote: > I need to create a list of values by referencing a series of...

cannot view all of text in large cell, even though I have it to w.
I have cell format to wrap text and it works fine to a p[oint then no more text is displayed....casn increase the size of the cell, but still only so much will display....rest of the cell show blank. Hi +the limit is 1024 characters. You can extend this with manually inserting linebrekas using aLT+ENTER -- Regards Frank Kabel Frankfurt, Germany sydme wrote: > I have cell format to wrap text and it works fine to a p[oint then no > more text is displayed....casn increase the size of the cell, but > still only so much will display....rest of the cell show blank. ...

access a cell in a range
I have variables: dim myrange as Range dim myString as String myrange specifies a specific column. How can I specify the cell in row 1 of myRange so I can set it's contents to myString? Hi there keyser soze, (Fan of the movie, eh? ;) <g> ) Like so .. myString = myRange(1).Value HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) <keyser_Soze@usa.com> wrote in message news:1128697509.320303.12550@g49g2000cwa.googlegroups.com... >I have variables: > > dim myrange as Range > dim myString as String > > myrange specifies a specific ...

private contacts moved to GAL DL's
I have a user who has many DL's in her private contacts, she wants to make them public in the GAL, what is the best method for this? We are using Exchange 5.5 and Outlook 2000. TIA, Nurv ...

Moving from cell to cell
Moving from cell to cell using arrow key stopped working. Help! Maybe hit Scroll Lock key... If Scroll Lock is on, the arrows still work, but in a different way. HTH Regards, Howard "crys0814" <crys0814@discussions.microsoft.com> wrote in message news:1B985BCA-BFB8-4718-8ED8-833BE78D8895@microsoft.com... > Moving from cell to cell using arrow key stopped working. Help! Hi there cry0814, It is possible that you have accidentally pressed your scroll lock key which disables your arrow keys. Try pressing it again and let us know your results Regard...

Two different cells
I have a file with more the 1000 names and addresses. My proble is that the firat and last names are in 2 differents cells. I i would like to put it in teh same cell. Is there anyway I csn do that? Please help too many names to re-type Carlos Assuming that Column A contains the first name and Column B the last name... =A1&" "&B1 OR =B1&", "&A1 ....depending on your preference. Hope this helps! In article <520FA0CA-1940-42E5-B84D-22BA15D59638@microsoft.com>, "Hombreck" <Hombreck@discussions.microsoft.com> wrote: > I have a...

imbed excel cell text in a shape
I need to insert a number into a shape that corresponds to a cell in excel. Kinda like the exploded parts view in a car repair manual. A callout shape with a text value "123" that is linked to cell A1 in file ***.xls. It would be nice to be able to auto generate the callouts from the excel file. shape 123 is cell A1, shape 124 is cell A2 and so on. Thanks what version of visio are you running? al "mystified" <mystified@discussions.microsoft.com> wrote in message news:80B4E390-190A-41D9-AC4A-F96C1DA6FB6E@microsoft.com... >I need to insert a number into a s...

Excel2000: Strange behaviour for one cell in workbook
Hi The situation: There is a Excel workbook used for registering working time in firm departments, created earlier. The worksheets are protected, and I don't have the password, as author isn't working here anymore. The workbook is stored as read-only on network resource, and monthly every department is copying it to local computer and filling the table. The table's body has 2 rows data for every employee with 31 columns for month's dates, and several protected summary columns. For every date, working hours or some code string is entered into according cell. One of codes, and ...

Conditional Formatting
Hi I need some help with conditional formatting. I'm trying to highlight those cells which have a plus sign in them. Can someone help with the formula to enter into the conditional formatting dialogue? Grateful for any assistance. Best Wishes Hi Colin if the Plus sign + is part of a text string Select the column range first then =FIND("+",A2:$A$160) change the range to your needs. If the + sign is alone in the cell, you don't need a formula, just select > Cell value is > then select Equal to> and type the plus sign in the third box. H...