Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
I need to know how to have two cells contain the same information, on the
same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
" Wonderer" wrote
I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? If you put in say, F1: =IF(A1="","",A1) F1 will reflect the input in A1. If A1 is cleared, F1 will display a blank: "" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Wondereer is looking for a change in EITHER cell getting reflected in the
other cell. In other words, there can be no dependencies other than what VBA imposes through code. Option Explicit 'Be Sure You Have Defined The Range FirstRef and 'SecondRef appropriately Private Sub Worksheet_Change(ByVal Target As Range) 'Code to make changes in one reference range get 'automatically made in another, bi-directionally Static IgnoreMyChange As Boolean If IgnoreMyChange Then IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("firstref").Address, "$", "") Then 'Target was firstref, set secondref IgnoreMyChange = True Range("SecondRef").Value = Range("FirstRef").Value IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("SecondRef").Address, "$", "") Then 'Target was firstref, set secondref IgnoreMyChange = True Range("FirstRef").Value = Range("Secondref").Value IgnoreMyChange = False Exit Sub End If End Sub "Max" wrote in message ... " Wonderer" wrote I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? If you put in say, F1: =IF(A1="","",A1) F1 will reflect the input in A1. If A1 is cleared, F1 will display a blank: "" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
one of my comment lines repeats -
2nd instance of ''Target was firstref, set secondref' should say 'Target was SecondRef, set FirstRef Sorry "William Benson" wrote in message ... Wondereer is looking for a change in EITHER cell getting reflected in the other cell. In other words, there can be no dependencies other than what VBA imposes through code. Option Explicit 'Be Sure You Have Defined The Range FirstRef and 'SecondRef appropriately Private Sub Worksheet_Change(ByVal Target As Range) 'Code to make changes in one reference range get 'automatically made in another, bi-directionally Static IgnoreMyChange As Boolean If IgnoreMyChange Then IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("firstref").Address, "$", "") Then 'Target was firstref, set secondref IgnoreMyChange = True Range("SecondRef").Value = Range("FirstRef").Value IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("SecondRef").Address, "$", "") Then 'Target was firstref, set secondref IgnoreMyChange = True Range("FirstRef").Value = Range("Secondref").Value IgnoreMyChange = False Exit Sub End If End Sub "Max" wrote in message ... " Wonderer" wrote I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? If you put in say, F1: =IF(A1="","",A1) F1 will reflect the input in A1. If A1 is cleared, F1 will display a blank: "" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Assuming A1 and D1 are the cells and you want to allow change in either cell:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub Regards, Greg "Wonderer" wrote: I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
My apologies for the mis-read.
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Ok, what I have is cell A1 and F1 are used in equations. They need to be
exactly the same, at all times. I want it simply so that I can change the amount in A1 and it will automatically change the amount in F1 and the subsequent equation will reflect that change. I am not "literate" in the code and terminology, so please explain it so a beginner can understand. Thank you Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know "Greg Wilson" wrote in message ... Assuming A1 and D1 are the cells and you want to allow change in either cell: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub Regards, Greg "Wonderer" wrote: I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Steve, If all you want is F1 to be dependent on A1, you just make the
formula in F1 say "=A1". If on the other hand, you want something which will cause either cell to automatically update when the other is changed ... then the Worksheet_Change event code which I gave earlier (pasted again below here) should do the trick. If you need help with getting to that point I would be happy to explain more, but will not go down that path if you are not interested. I am going on vacation shortly as well, others might help you further. Regards, Bill Option Explicit 'Be Sure You Have Defined The Range FirstRef and 'SecondRef appropriately Private Sub Worksheet_Change(ByVal Target As Range) 'Code to cause a change in one reference range to be automatically 'carried over to another, in either direction. Static IgnoreMyChange As Boolean ' Don't want steps in this routine to fire 'when we set second cell = first, a static 'variable is one way to keep tabs on 'what's happening. If IgnoreMyChange Then 'This sub was called through the VBA operation, not user input IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("firstref").Address, "$", "") Then 'Target was firstref, set secondref to this IgnoreMyChange = True Range("SecondRef").Value = Range("FirstRef").Value IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("SecondRef").Address, "$", "") Then 'Target was SecondRef, set FirstRef to this IgnoreMyChange = True Range("FirstRef").Value = Range("Secondref").Value IgnoreMyChange = False Exit Sub End If End Sub " Wonderer" wrote in message ... Ok, what I have is cell A1 and F1 are used in equations. They need to be exactly the same, at all times. I want it simply so that I can change the amount in A1 and it will automatically change the amount in F1 and the subsequent equation will reflect that change. I am not "literate" in the code and terminology, so please explain it so a beginner can understand. Thank you Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know "Greg Wilson" wrote in message ... Assuming A1 and D1 are the cells and you want to allow change in either cell: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub Regards, Greg "Wonderer" wrote: I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
And Greg's code was way better than mine by the way ... nice 'n' elegant ...
I'll get there someday on this slow march from power-user to programmer ... "Give a man a fish, feed him for a day. Teach him to fish, and he give's up programming" "William Benson" wrote in message ... Steve, If all you want is F1 to be dependent on A1, you just make the formula in F1 say "=A1". If on the other hand, you want something which will cause either cell to automatically update when the other is changed ... then the Worksheet_Change event code which I gave earlier (pasted again below here) should do the trick. If you need help with getting to that point I would be happy to explain more, but will not go down that path if you are not interested. I am going on vacation shortly as well, others might help you further. Regards, Bill Option Explicit 'Be Sure You Have Defined The Range FirstRef and 'SecondRef appropriately Private Sub Worksheet_Change(ByVal Target As Range) 'Code to cause a change in one reference range to be automatically 'carried over to another, in either direction. Static IgnoreMyChange As Boolean ' Don't want steps in this routine to fire 'when we set second cell = first, a static 'variable is one way to keep tabs on 'what's happening. If IgnoreMyChange Then 'This sub was called through the VBA operation, not user input IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("firstref").Address, "$", "") Then 'Target was firstref, set secondref to this IgnoreMyChange = True Range("SecondRef").Value = Range("FirstRef").Value IgnoreMyChange = False Exit Sub End If If Replace$(Target.Address, "$", "") = _ Replace$(Range("SecondRef").Address, "$", "") Then 'Target was SecondRef, set FirstRef to this IgnoreMyChange = True Range("FirstRef").Value = Range("Secondref").Value IgnoreMyChange = False Exit Sub End If End Sub " Wonderer" wrote in message ... Ok, what I have is cell A1 and F1 are used in equations. They need to be exactly the same, at all times. I want it simply so that I can change the amount in A1 and it will automatically change the amount in F1 and the subsequent equation will reflect that change. I am not "literate" in the code and terminology, so please explain it so a beginner can understand. Thank you Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know "Greg Wilson" wrote in message ... Assuming A1 and D1 are the cells and you want to allow change in either cell: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub Regards, Greg "Wonderer" wrote: I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
"William Benson" wrote
Steve, If all you want is F1 to be dependent on A1, you just make the formula in F1 say "=A1". .. It certainly sounds that way to me. So perhaps the initial interp on the OP and suggestion given wasn't that far off, afterall .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Your powers go way beyond Excel Guru, Max -- to mind reader!
Cheers. "Max" wrote in message ... "William Benson" wrote Steve, If all you want is F1 to be dependent on A1, you just make the formula in F1 say "=A1". .. It certainly sounds that way to me. So perhaps the initial interp on the OP and suggestion given wasn't that far off, afterall .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Ok, what I have is cell A1 and F1 are used in equations.
They need to be exactly the same, at all times. ... What perhaps is not so clear from the OP's response to Greg is why the equations cannot be (re)structured to point to only A1 ? -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
The code I gave you allows you to change or delete either the value in A1 or
D1 and the change will be reflected in the other cell - i.e. the behavior is bidirectional. Although it doesn't sound like this is what you want, I will first describe how to implement it assuming you are not familiar. Instructions: 1) Change the cell references to suit in the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub 2) Copy the code. 3) Hold the mouse pointer over the sheet tab and right mouse-click. 4) Select View Code. This will take you to the sheet's code module. 5) Paste the code. 6) Test it out: Change or delete the values in either cell. Note that the code also alows you to select multiple cells inclusive of either of the above and delete them and it will still work. It was deliberately written to handle this situation. All of the above said, it sounds as though you only need a simple worksheet formula (or formulae). If you only intend to be changing the value in cell A1 and never in D1 but you want D1 to reflect A1's value, then, as the others have already provided, the formula: "=A1" or "=If(A1 = "", "", A1) should do. If this doesn't do what you need then I suggest posting the formula(e) and cell address(es). Regards, Greg "Wonderer" wrote: Ok, what I have is cell A1 and F1 are used in equations. They need to be exactly the same, at all times. I want it simply so that I can change the amount in A1 and it will automatically change the amount in F1 and the subsequent equation will reflect that change. I am not "literate" in the code and terminology, so please explain it so a beginner can understand. Thank you Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know "Greg Wilson" wrote in message ... Assuming A1 and D1 are the cells and you want to allow change in either cell: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub Regards, Greg "Wonderer" wrote: I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Greg - just a heads up:
If I modify your code with a counter variable to see how many times it is called, then it looks like this Private Sub Worksheet_Change(ByVal Target As Range) Static cnt As Long If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If cnt = cnt + 1 Debug.Print cnt End Sub then I change the value of A1; in the debug window, the last couple of lines a 200 201 202 203 204 205 (thank goodness it finally stops - <g) so you might want to make it like Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler Application.EnableEvents:=False If Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If ErrHandler: Application.EnableEvents:=True End Sub -- Regards, Tom Ogilvy "Greg Wilson" wrote in message ... The code I gave you allows you to change or delete either the value in A1 or D1 and the change will be reflected in the other cell - i.e. the behavior is bidirectional. Although it doesn't sound like this is what you want, I will first describe how to implement it assuming you are not familiar. Instructions: 1) Change the cell references to suit in the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub 2) Copy the code. 3) Hold the mouse pointer over the sheet tab and right mouse-click. 4) Select View Code. This will take you to the sheet's code module. 5) Paste the code. 6) Test it out: Change or delete the values in either cell. Note that the code also alows you to select multiple cells inclusive of either of the above and delete them and it will still work. It was deliberately written to handle this situation. All of the above said, it sounds as though you only need a simple worksheet formula (or formulae). If you only intend to be changing the value in cell A1 and never in D1 but you want D1 to reflect A1's value, then, as the others have already provided, the formula: "=A1" or "=If(A1 = "", "", A1) should do. If this doesn't do what you need then I suggest posting the formula(e) and cell address(es). Regards, Greg "Wonderer" wrote: Ok, what I have is cell A1 and F1 are used in equations. They need to be exactly the same, at all times. I want it simply so that I can change the amount in A1 and it will automatically change the amount in F1 and the subsequent equation will reflect that change. I am not "literate" in the code and terminology, so please explain it so a beginner can understand. Thank you Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know "Greg Wilson" wrote in message ... Assuming A1 and D1 are the cells and you want to allow change in either cell: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub Regards, Greg "Wonderer" wrote: I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Thanks to all of you for your help. Initially, I had tried using =a1 but for
some reason it would not work. That is why I posted here. When I went back and tried it again after reading some replies, it worked. I really do appreciate the responses though, because some groups get no response at all. -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know " Wonderer" wrote in message ... I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplication
Thanks for the heads up Tom. I know better but it worked well enough that I
didn't give it a second thought. You never cease to amaze me with how sharp you are. I sure wish I had your gift. Best regards, Greg "Tom Ogilvy" wrote: Greg - just a heads up: If I modify your code with a counter variable to see how many times it is called, then it looks like this Private Sub Worksheet_Change(ByVal Target As Range) Static cnt As Long If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If cnt = cnt + 1 Debug.Print cnt End Sub then I change the value of A1; in the debug window, the last couple of lines a 200 201 202 203 204 205 (thank goodness it finally stops - <g) so you might want to make it like Private Sub Worksheet_Change(ByVal Target As Range) On Error goto ErrHandler Application.EnableEvents:=False If Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If ErrHandler: Application.EnableEvents:=True End Sub -- Regards, Tom Ogilvy "Greg Wilson" wrote in message ... The code I gave you allows you to change or delete either the value in A1 or D1 and the change will be reflected in the other cell - i.e. the behavior is bidirectional. Although it doesn't sound like this is what you want, I will first describe how to implement it assuming you are not familiar. Instructions: 1) Change the cell references to suit in the following code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub 2) Copy the code. 3) Hold the mouse pointer over the sheet tab and right mouse-click. 4) Select View Code. This will take you to the sheet's code module. 5) Paste the code. 6) Test it out: Change or delete the values in either cell. Note that the code also alows you to select multiple cells inclusive of either of the above and delete them and it will still work. It was deliberately written to handle this situation. All of the above said, it sounds as though you only need a simple worksheet formula (or formulae). If you only intend to be changing the value in cell A1 and never in D1 but you want D1 to reflect A1's value, then, as the others have already provided, the formula: "=A1" or "=If(A1 = "", "", A1) should do. If this doesn't do what you need then I suggest posting the formula(e) and cell address(es). Regards, Greg "Wonderer" wrote: Ok, what I have is cell A1 and F1 are used in equations. They need to be exactly the same, at all times. I want it simply so that I can change the amount in A1 and it will automatically change the amount in F1 and the subsequent equation will reflect that change. I am not "literate" in the code and terminology, so please explain it so a beginner can understand. Thank you Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know "Greg Wilson" wrote in message ... Assuming A1 and D1 are the cells and you want to allow change in either cell: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Range("D1") = Range("A1") ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then Range("A1") = Range("D1") End If End Sub Regards, Greg "Wonderer" wrote: I need to know how to have two cells contain the same information, on the same sheet, so that when I change on cell, the other will reflect that change. How do I do that? -- Steven Shelton There are two secrets to success in life 1) Never tell anybody everything you know |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplication | Excel Worksheet Functions | |||
Duplication | Excel Discussion (Misc queries) | |||
Duplication | Excel Discussion (Misc queries) | |||
How-to Prevent Value Duplication | Excel Discussion (Misc queries) | |||
Data duplication | Excel Programming |