Help needed on making multiple cells "constant"

[derrty6232]

Hello, I am new to this message board.

How do I make it so that when i change the value or label of one cell, a number of other cells change to the same value or label? The cells do not contain formulas or functions, just constants. Thanks for any help in advance.

 

[Pekkavee]

If you have your value in A1

so in B1 write

=A1

Or did I miss something?

Pekka


BTW. Wellcome to the board. This is the best Excel board where you get answer to all questions.

 

[derrty6232]

Actually, I have 3 cells, lets call them A1, A2, and A3. I want to make it so that if I change A1 then A2 and A3 = the value in A1, likewise, if i change A3, I want A1 and A2 = the value in A3, so basically I to be able to change any 1 of the three cells and have the other 2 cells to have the same value.

 

[Pekkavee]

Here you need VBA.

Are you familiar with that?

Pekka

Anyhow, this need of your seems to be quite special.
Can you explain to us what you are really doing? And what you want to gain with this.
Maybe there is an other solution for your needs.

 

[derrty6232]

Thanks for the welcome.
There's nothing really special about what I'm doing, just doing an Information Systems assignment. Hm...VBA eh... not too familiar with this app yet... Thanks for the help anyways. If I find a solution I'll be sure to post it.

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Intersect(Target, [A1:A3]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">' Monitor for changes to only A1:A3</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Row
    <SPAN style="color:#00007F">Case</SPAN> 1
        [A2] = [A1]: [A3] = [A1]
    <SPAN style="color:#00007F">Case</SPAN> 2
        [A1] = [A2]: [A3] = [A2]
    <SPAN style="color:#00007F">Case</SPAN> 3
        [A1] = [A3]: [A2] = [A3]
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

When in your workbook, hit Alt+F11 - that'll open the VB Editor.

Look in the left hand pane and find your workbook name, then underneath that the sheet name where your A1/A2/A3 is located.

Click on it so it is selected. Then copy/paste the above code in the big white space in the center/right.

Close out that screen.

Now, any change to A1 or A2 or A3 should be reflected as you wanted.

From now on, when opening your workbook, you will be alerted that there are macros. If you want this code to function you must answer in the affirmative.

 

[Pekkavee]

Just Jon is great

Pekka

 

[derrty6232]

o wow, thanks jon.