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.
_________________
Is there any way that I can have more than one cell use this code instead of just column 3 as in the example?
Thanks,
Kai
marvelous, didn’t know it was possible.
it is exactly what I was in need.
thx,
Rafael
Works great, but….. One question. If you select an item in err, what is the best way to remove it? I went to the formula bar and deleted the duplicate item but it gives me an err ” The Value you enter is not Valid” “A user has restricted values that can be entered into this cell”
Also, when I select more than two items I receive the err ” The value in this cell is invalid or missing, ….”
Thanks for your help,
Juanita
Juanita, if you want users to be able to edit the cell, you can change one of the Data Validation settings. On the Error Alert tab, in the Data Validation dialog box, remove the check mark for ‘Show error alert after invalid data is entered’
I am trying to copy and paste the code for the same cell with comma separators to work for column T (which is column 20?) And it isnt working for me.
I even tried applying to the entire worksheet and deleting the lines with the red dots, but that isnt working either.
Any tips?
Thanks,
Cameron
I am a little puzzled as to how to use filters along with the multi select drop down lists to search or isolate particular values. My programming skills are very limited so I am having some difficulty working this out. Other users of my spreadsheets have very little knowledge of how it works so I need it to be as easy and foolproof as possible. Would it be easiest to used the column option or the single cell, and will the filter like this?