Data Validation: Lists | Linking Cells

[Paradigm_Shift]

I have a dropdown list in a cell on, say, sheet1. Then for example, on sheet2 I need the same drop down list, BUT if a value is selected in the dropdown list on sheet1, when I switch to sheet2, I want the value selected to populate the same cell that has the dropdown.

Conversely, if a value is selected from the dropdown on sheet2, the cell containing the dropdown on sheet1 should reflect that value.

Basically I want some version of a circular reference with a formula in each cell similar to below:

(In A1, sheet1) =Sheet2!A1
(In A1, sheet2) =Sheet1!A1

BUT they also should have a dropdown. When a new value is selected from the list, I still want the formulas to somehow be in there...

How can I achieve this result?

 

[Peter_SSs]

You would need some vba to do that. Try this in a copy of your workbook.

Note that I have changed one of the cell references to demonstrate that it doesn't need to be the same cell on each worksheet.

1. Right click the name tab of Sheet1 and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

<font face=Courier New><br><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)<br>    <SPAN style="color:#00007F">Const</SPAN> sh1InputCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1"<br>    <SPAN style="color:#00007F">Const</SPAN> sh2InputCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B3"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(sh1InputCell)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        Sheets("Sheet2").Range(sh2InputCell).Value = Range(sh1InputCell).Value<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

3. In the left hand pane of the vb window, double click Sheet2 in the Microsoft Excel Objects section of your project (or go back to your workbook and right click Shet2's name tab and choose 'View Code')

4. Paste this code in the Sheet2 module window,

<font face=Courier New><br><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)<br>    <SPAN style="color:#00007F">Const</SPAN> sh1InputCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1"<br>    <SPAN style="color:#00007F">Const</SPAN> sh2InputCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B3"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(sh2InputCell)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        Sheets("Sheet1").Range(sh1InputCell).Value = Range(sh2InputCell).Value<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

5. Close the vb window and try changing either of the Data Validation cells and check the other one.

 

[Paradigm_Shift]

WOW! Works perfectly. Thank you so much!