Forum Discussion
How to create a drop-down list and be able to select more than one entry.
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.
mathetes, how can we keep a record?
At another sheet? Inside comments or notes?
I'm genuinely curious in where can I search for a guide or some examples of how could I keep these changes in check.....how can we keep a record?
At another sheet? Inside comments or notes?All you've shown us so far is the original request to use the "drop down" method to allow multiple entries.
The answers to your questions above depend very much on the bigger context here. What is the whole workbook about? What is it tracking? What's the input? What's the output or desired result? That single data field you've asked about (whether with single entries or multiple) is a small part of a bigger job, one entry among many--presumably--and without knowing what that bigger picture is, I don't think we can say much more than we've said.
One broader conceptual framework to consider is keeping a history of transactions (or of entries, whatever they are). My background is as the director of a database for managing HR and compensation (wages, salaries, taxes, etc) data for a major corporation. People move locations, change job titles, change number of dependents (for benefits), etc., etc. We did not keep a single "current" profile of all of those variables; rather, we'd have history, consisting of employee ID, date, followed by the new status or situation, whatever it was [new job code],[new location code],[new whatever...]. The "current profile," if we needed it, consisted of the most recent of all the historical changes in each of those various categories.
You'll see a reference to "data integrity" in one of my postings above--that refers to an important concept in data processing. Basically, you want a design where all of the current data fits together in a reliable, trustworthy manner. So, for example, you want to design the whole system so that it's not possible to say in one current record that a product is both "New" and "Discontinued" -- it was new at one time (which is why dates are important) and then discontinued, but not both states at the same time. [Granted, your first request does not include the "Discontinued" as one of the options, but you see my questions about the other potentially troublesome combinations; my point here is just to underscore the importance of clarity and reliability.]
I agree.
