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

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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

he
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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

"David L" wrote in message
...
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

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"
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.

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

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"
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.

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.

F5SpecialFormulasOK

With those cells cells selected hit EditCopy then Paste SpecialValues to
wherever you want.

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

With those selected cells EditClear 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"
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

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"
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.