[Solved] Dependant DropDown

[quarkrad]

Re attached. I have two drop down lists in my Input Sheet. At the moment any option from the drop down list can be chosen in E7 or G7 - both lists are independent. However, there is a dependency in that there is no choice if fly is chosen in either list. What I would like to achieve is if the fly option is chosen in either E7 or G7 then fly appears in both E7 and G7. Can this be done(?) - I have tried to find out but at the moment I am stuck.

[Villeroy]

If G7 depends on E7, then you don't need a choice in G7.
G7: =VLOOKUP(E7;$P$6:$Q$10;2;0) shows a value of Q6:Q10 depending on the choice in E7

P.S. Example for dependent drop-downs: download/file.php?id=333 where multiple cities are selectable depending on the chosen country. The expressions for validation lists in column B are formula expressions returning an array of values depending on the left neighbour's value

[quarkrad]

Thank you - that works .... but I've lost the ability to make a choice in G7. How would I choose run in E7 and cook in G7? Or walk in E7 and slow in G7? I'm looking to have independent choices in both lists (that is they act like two independent drop down lists) except for when you choose fly in either. If you choose fly in either list then both list will show fly.

[MrProgrammer]

What I would like to achieve is if the fly option is chosen in either E7 or G7 then fly appears in both E7 and G7. Can this be done?

You want G7 to become fly if E7 is set to fly. And you want E7 to become fly if G7 is set to fly. So E7 and G7 are mutually-dependent cells. The computation model that spreadsheets use does not support mutually-dependent cells (circular references).

OpenOffice does offer option Calc → Calculate → Iterative references which seems to allow circular references but I've never seen any documentation about how the cell values are then calculated (because the standard computation model can't be used). I don't know if you could achieve what you want with Iterative references. I never use that abomination myself and cannot recommend it to you. You could probably program this with macros, but I cannot help you with that.

I'm looking to have independent choices in both lists (that is they act like two independent drop down lists) except for when you choose fly in either. If you choose fly in either list then both list will show fly.

That's different. Now you want the dropdown lists, not the cell values, to be mutually dependent. You can do that: For E7 set Data → Validity to IF(G7="fly";$P$10:$P$10;$P$7:$P$10) and For G7 set Data → Validity to IF(E7="fly";$Q$10:$Q$10;$Q$7:$Q$10). But now once you've set the cells to fly you're locked in to that and can only get the other choices back by clearing the cells.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[quarkrad]

Thank you for your help - I will use as suggested.

[Villeroy]

Instead of synchronising 2 lists, you could merge them into one list.