Control One Variable from Multiple Locations.

**Baseball17Bucks** · 01-24-2015, 04:52 PM

Hi There,

Let's say I have two drop down list boxes on two different worksheets "Worksheet 1" and "Worksheet 2". Each drop down list box can say "Project A" or "Project B".

If a user is on "Worksheet 1", and they choose "Project A" in the list box, I want the list box on "Worksheet 2" to change to "Project A" as well.

If a user is on "Worksheet 2", and they choose "Project A" in the list box, I want the list box on "Worksheet 1" to change to "Project A" as well.

If a user is on "Worksheet 1", and they choose "Project B" in the list box, I want the list box on "Worksheet 2" to change to "Project B" as well.

If a user is on "Worksheet 2", and they choose "Project B" in the list box, I want the list box on "Worksheet 1" to change to "Project B" as well.

I would like the flexibility to have this work on additional worksheets (I'm illustrating the basic example, but in actuality I have 4 worksheets with 4 list boxes).

Hopefully this makes sense - thanks!

JF

**FDibbins** · 01-24-2015, 05:47 PM

Hi, welcome to the forum

What you want cannot be done with regular formulas or drop downs

A cell can contain either data or a formula. A drop down puts data into a cell, if you have a formula in that cell, the DD will over-write the formula with the selected data.

As an alternative, you might try to use data validaton on sheet 2 so that it will only allow a user to select what is already selected on sheet 1. Note, that if you change what is in sheet 1, the DV on sheet 2 will not trigger until the user tries to change it. I dont think this will work on both sheets looking at each other, bt it will work on 1 sheet looking at the other

Another option mighgt be to use conditional formatting to change 1 sheet ce3ll to turn red (for instance) ifr it does not contain teh same as the other sheet. This option can be done both ways

**JBeaucaire** · 01-24-2015, 05:50 PM

The only way to implement multi-linked-sheet value reflection the way you're describing would be to employ VBA, a Worksheet_Change event macro imbedded in each worksheet watching the specific cells where you want this done and the VBA would actually transfer the changes from the activesheet to all the other sheets using the addresses you specify.

If you're familiar with VBA at all, this would not be difficult. If you're not, this will seem much harder than it really is. VBA is a useful tool if you're not scared of macros active your projects. Not everyone is comfortable with it.