Forum Discussion
multi-select drop-down list
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.
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.