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.
_________________
@rmk, can the other users run any other Excel macros? Perhaps their security settings are High, and that’s blocking the macros, without any warnings, on their computers.
Debra,I am not a programmer. This is the first time I am using the macros. I am not able to change the values( one, two, three, for, five) in the SameCell sheet. Could you please let me know where in the code I should go and change the values(list of drop down). thank you.
Hello Debra,
I have downloaded the workbook and tried using it before even copying it to my sheet and it does not appear to be working on the workbook I downloaded. The one I am interested in using is the fill down a column option.
Thanks for your help,
Heidi
@SAC, select the cells with the drop down lists.
On the Excel Ribbon, click the Data tab
Click Data Validation, and you’ll see the list of items in the Source box.
You can change the list there, then click OK to save and close.
@Heidi, you might need to change your macro security settings, and then allow macros to run in the workbook.
There are instructions here: Excel macro security settings
Debra, This is absolutely fabulous! Thanks.
I would also like to have the ability to REMOVE an item from a comma separated list. For example, in your workbook, on the SameCell sheet, I have a cell populated with “Two, Four, Three, Three”. This happened because I selected ‘Three’ from the dropdown list twice. I’d like the behavior altered such that an option in the dropdown list can occur once and only once in the target cell. Should an option be selected; if not present in the target, it should be added, if it IS present, it should be removed. Can you help?