Forum Discussion
Solved
Excel Data Validation List multiple choice WITHOUT using VBA
Hi All, I'm looking to the community again to assist with a challenge I'm trying to resolve. Whilst these posts outline that multiple choice isn't possible (Select Multiple Items from a Drop Down...
1. Create a constant for your list and bank in the name manager:
'colors ={"red";"green";"blue";"yellow";"purple"}2. Refer to the named item in the sheet creating a spill.
=colors3. Create a 'remaining' spill by identifying what has been selected:
Selections being the range with your data validation menus.
=FILTER(G4#,ISERROR((XMATCH(G4#,Selections))))4. Point the first cell with data validation to the colors spill where all choices are available.
e.g. =$G$4#
Point subsequent data validation lists to the remaining spill.
e.g. =$H$4#
5. Enjoy
Post corrected - thanks for the heads up!
Your example is helpful, but that helpfulness is limited because it's a bit too abstract or conceptual. Could I ask you to use examples OTHER THAN "option 1", "option 2" etc and accompany that set of examples with a description of the actual context (or one that parallels the actual, if the actual is too confidential). Seeing something more specific or concrete could help grasp the bigger picture.
I'm attaching a demo file that I've created to show what I call "cascading data validation" where second and third selections depend on those that precede. That is, the first selection determines what you see on the second; the second, what you see on the third. I don't know if this is in any way comparable to what your actual situation is, but it does not use VBA. It DOES make use of FILTER and UNIQUE, relatively recent dynamic array functions.
