Linking data validation cells

Hi All,

I have a spreadsheet with 2 tabs, one tab mainly consists of data, the second tab has analysis of this data. But the way the data is analysed can be changed via a number of switches on the analysis tab. These switches are just data validation lists with drop down menus (e.g. ON or OFF) to make it easy for the user. This is all fine and works well, but I have two sorts of people that use the spreadsheet, people who only care about the data, and people who only care about the analysis. But both of these users want access to one of these switches. Now yes, all the users have to do is go to the analysis tab and alter the switch, but just to help speed up the use of the sheet I was just wondering if there was a way to essentially have the same switch on both tabs. So on both tabs would be a drop down menu with the same list items, but changing one on one tab also changes the other on the other tab, and vice versa. For the life of me I can't think of a way to do this without VBA (I can't use VBA on this sheet unfortunately), it's kind of a circular reference problem I guess. But was just wondering if any clever clogs can think of something cunning!

Many thanks!

Chris

Hi Marvin,

Thanks for the reply. Looks interesting, but I'm locked to Office 2010 as that's what my client uses. Can't see them upgrading anytime soon either. Slicers also seem more to do with filtering data, but I'm changing the way the data is analysed with my "switches", rather than just changing what's displayed. I was just hoping there was a way to link two switches on different tabs, but I suspect it's not possible.

I'm sure the client will upgrade, but it's part of a big network and so upgrades take a long (long) time to get approved. I'd need the switch to be workable on both tabs, so if that's not possible (without VBA) I'll just leave it as is. It was only a "nice to have" feature to speed things up. Thanks for your input!

Chris