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 List in Excel (trumpexcel.com)) without using VBA. Following some small nuggets of information online I've been able to create a mini-example of what I'm trying to do but it's just not doing it exactly as I'd want it (I'm sure others would like this post if this could be cracked).
I've followed one of the posts here (Here is how to have a multiselect dropdown without VBA : r/excel (reddit.com)) and managed to get it working. The query I have would be whether it is possible to modify the formula so that when selecting from the dropdown it doesn't show the concatenated values possible and instead just the ones that haven't been selected. For example using the attached excel, once I have selected a value in column d 'Dropdown' the remaining options are shown as a concatenated value (select This is option 2 in 'D5' - in the available options this shows'
This is Option 2; This is Option 1
This is Option 2; This is Option 3
etc).
What I'm trying to achieve is just show the remaining options that haven't been selected rather than showing a concatenated value as when 4 values have been selected this becomes quite unwieldly to understand/use (e.g., with 'This is Option 2 in D5 - the available options to show:
This is Option 1,
This is Option 3)
Edit (2023-09-11): Realised the full message didn't post - corrected.
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"
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
The problem is that selecting an item from a data validation list replaces the current value of the cell. There is no way to get around that without using VBA - the usual solutions that you can find on the internet use VBA to manipulate the cell value after it has already been replaced.
The solution that you found on Reddit uses a range as source for the data validation list that is dynamically populated with the resulting values if multiple selection was possible. That way, the selected value can replace the current value. You'll have to live with this since you cannot use VBA.
Sorry to rain on your parade!
The query I have would be whether it is possible to modify the formula so that when selecting from the dropdown it doesn't show the concatenated values possible and instead just the ones that h
If you'd like to continue the conversation, we're waiting with bated breath for you to finish the sen
- 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.
