Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 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 |
#10
|
|||
|
|||
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" wrote in message ... 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 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 |
#11
|
|||
|
|||
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" wrote in message ... 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 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 |
#12
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
move cell contents
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I move data from one cell and add to another? | Excel Worksheet Functions | |||
Can I use the contents of a cell to satisfy the result_vector arg. | Excel Worksheet Functions | |||
How to move cell to cell with tab key | Excel Worksheet Functions | |||
Insert new row as cell contents change | Excel Discussion (Misc queries) | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) |