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
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
It seems to me that the method that Patrick2788 has given you satisfies (or comes very close to satisfying) what you want. I know I'm saving it to my file of "Great solutions"
