Forum Discussion
Excel Data Validation List multiple choice WITHOUT using VBA
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
mathetes Patrick2788 HansVogelaar thanks for your responses and input so far - reading the above it may not be possible what I'm looking to do - but none the less I'll put a copy of a more realistic example than just 'Option 1, 2 and 3 etc'. I've updated the excel demo included in the original post replace is with fictionary book titles. Ultimately I want the user to be able to select books that they like. Therefore they navigate to column D (imagine there is a another C for name for the example purposes) and they select the books that they're a fan of. I cannot screenshot it but when the dropdown is selected in the first instances this is fine, for example they select "Exploring the Cosmos: A Journey Through Space and Time;" when they then click the dropdown arrow rather than it showing the remaining four book titles it is showing a their first option + books they haven't selected. This really only is an issue in this scenario because the book titles are long which means not all options become visible - especially when a second book is selected. Where as what I'm really after is when the user selects Book 1 it and then again selects the drop down it only shows the outstanding books not selected.
May not be possible but thought I'd try my luck here!
I don't think that a multi-select drop-down with book names is a good idea - it becomes unwieldy.
IMHO, you'd be better off with multiple cells with single-select drop-downs, where each choice restricts the following ones, as demonstrated by mathetes and Patrick2788