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
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!
