06-26-2020 11:29 AM
06-26-2020 11:29 AM
I would like to know if Excel supports the ability for a drop-down list to be multi-select. So for example, I would like a load a list of States into a drop-down list and allow the user to select multiple States, which will then display in the cell.
Next, I would like the auto-filter capability to allow me to select a single State and then show the rows that have that State as one of the selected choices in the cell.
If not via a drop-down list, is there another way Excel supports this capability?
06-26-2020 04:55 PM
06-29-2020 09:31 AM
To a certain extent your request almost goes against the purpose of the drop-down lists (that purpose also going by the name "Data Validation")...
So let me ask you a question to see if there might be another way to accomplish whatever it is that you ultimately want to do. The question: is there a maximum number of states you would anticipate a person selecting for any one instance? If so--let's assume that there's a max of 10--would it be possible to have ten columns, in each one of which you can select the name of a single state, but the list gets shorter as each one is used in a prior selection?
So I guess what I'm really asking is: what's the larger context here? What's the larger purpose that is being served by selecting multiple states?:
There is almost always a variety of ways within Excel to accomplish a given purpose; I just want to make sure we're not limiting ourselves at the start by assuming we have to go in one direction.
06-30-2020 02:12 AM - edited 06-30-2020 06:23 AM
I have seen a pretty close functionality where you can select multiple options from a Drop List and every option is added next to the previous selection with a comma separating them. If this is what you want then you can use any of the static techniques (Text To Column) or Dynamic ones (Dynamic array functions) or Split by delimiter in Power Query to separate the result into multiple columns.
However, I personally think that by selecting multiple options, you are defeating the purpose of Data Validation. Further more, sorting and filtering may yield incorrect results.
If you want to read the Blog I mentioned, it is written by fellow Excel MVP Mynda Treacy, and here is the link:
Hope that helps
06-30-2020 04:44 AM
Essentially I am trying to use Excel as a "vendor database" as opposed to building something in Access or using another vendor tool, such as Salesforce. We are tracking a list of vendors and their solution and want to know what States they have deployed that solution in. So Vendor A, for example, may be active in 5 different States.
The reason for attempting to capture all this information in one column is so we can filter on that column by State to see a list of vendors within a given State. Creating separate columns to capture the States defeats that purpose. I can repeat the vendor row for each State they are in and filter that way (what we are doing currently), but was curious if there was a different way to reduce the number of rows and repetition of data.
06-30-2020 05:49 AM
So here's another solution, one which will preserve the "Data Validation" purposes of the drop-down boxes.
I'm presuming that each row of data on Vendors has other info--name of a contact, corp address, whatever, so you're very legitimately wanting to avoid being redundant with all that data.
This is a simple "relational database" where the key to each of two tables is the vendor ID. One of the tables contains all that vendor specific data like contact name, corp HQ, whatever; the other is just a row for each state the vendor operates in.
And then, using the new FILTER function [for which you do need the most recent version of Excel] it's easy to create a list of the vendors and all the information on them that you want, who operate in, say, NY... and then VLOOKUP on the Vendor DB gets whatever other info you want in your new "filtered list"
To change the list in the attached file, just enter a state abbreviation in the pale yellow cell. All the rest will be done for you. And you can add other vendors and other states to your heart's content; because they're formal Excel Tables, the formulas referring to them will accommodate your additions.
But you do need the newest release of Excel.