Forum Discussion
How to create a drop-down list and be able to select more than one entry.
I know how to create the drop-down list, but I want to know how to be able to select multiple enters.
Here is my list:
| New |
| SPR |
| Enhancement |
| Bug |
What are the steps.
Thank you,
7 Replies
I would like to do this, too. How can it be done? Data integrity is not an issue down the line as long as data is there in the one cell. Just need a checklist where end user could pick multiple entries out of a picklist 6 deep. I except each entry would go on it's own line within the cell. Seems more efficient to allow this rather than any of the work-arounds suggested.
An alternative is to use VBA to allow multiple entries to be selected in one cell. See Excel Data Validation - Select Multiple Items
This will only work in the desktop version of Excel for Windows and Mac, not in Excel Online, nor on other platforms.
HansVogelaar That's an interesting solution. And I can imagine a few situations where it might be helpful. Perhaps the situation presented by Jesus_Hernandez is one such.
HOWEVER, and it's a big however: there's a potential downside to allowing multiple entries in a single cell. Depending on what use will be made of the entries down stream, allowing multiple entries can create havoc with regard to data integrity.
For example, in the original posting in this thread, Jesus_Hernandez shows two of the four possible responses as "Enhancement" and "Bug" -- although it's remotely possible that an enhancement could be a bug, there's also an inherent logical inconsistency there. So if that field is being used as anything other than an answer to idle curiosity--i.e., if the entry has any real significance in downstream processes--it would be better from a data integrity point of view not to allow them to be combined. Same for "New" and "Enhanced": which is it? It can't be both/and, not if the labels have any true meaning; has to be either/or.
The alternative? Have a date field on each record, or even a date and time, and keep history of entries, so that we know that what was once reported as an enhancement turned out to be a bug or to have a bug, etc. Keeping the history is important, but don't muddy it by combining two different states of affairs into a single row.
The steps are to create a list that contains the possible multiples. In other words, you can only select one item from a drop down list--that's why it's also called "Data Validation"--but if there are only four items on the list, you can fairly easily create combinations of whatever combinations are possible.
The other possibility--although you don't mention it--is that what you really need is a secondary (or cascading) set of data validations. Here's a sample of how that could be done.
