You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.
Single Selection Drop Down
By default, data validation drop downs only allow you to select one item.
In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.
You can display all the selected items across a row, or down a column, or keep them in the same cell.
Keep reading, to see some examples of this technique, and how it works.
There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link f
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
This was a great help! I was able to start out with what you had provided. Then the above string answered some of the additional items/features I was looking for. Great resource, and thanks.
Hi Debra,
This works perfectly until I save & reopen it. When I reopen it reverts to one selection only per cell. I have copied the code from the same cell tab, replaced it with the appropriate column no & saved as macro-enabled. Any Ideas?
Thanks,
Peter
@Peter, make sure to enable macros when you open the file — there’s a security warning bar just above the formula bar.
If you don’t see the security warning bar, change your macro settings to “disable all macros WITH notification”. (Office button, Excel Options, Trust Center, Trust Center Settings)
Hi Debra.
A very silly question I know, but……….
The list does exactly what I want it to do. However, how do I apply my own (very long) list instead of the default one, two, three, four five?
Thanks very much.
I’m jump in in the middle of this thread.
Kudos for your Same Cell and Hide Previous routines. I have implemented the Same Cell (using comma separation) successfully, but my Hide Previous routine needs to work differently than the way you’ve implemented yours. I want to implement the Hide Previous the next time I click the drop-down in the SAME CELL and remove all previous choices used in THAT CELL from the available choices. This makes for a more effective pick list becuase there’d never be a need to pick the same choice more than once on a given day (each row).
When I get to a new row, my full list of available choices starts all over again.
COMMENT: I am not a VB programmer but I assume that there is not a way to open up a combo box of multiple choices and either control-click on any of them or click on checkboxes associated with each. That would get around all these INDEX, OFFSET and MATCH functions.
But cheers to you for figuring out how to do it and thanks for your many useful contributions.
Hi Nik,
Use a named range as the data validation list source, as described here:
http://www.contextures.com/xlDataVal01.html