Multi-level data validation (perhaps using VLOOKUP?)

Hello all,

I am trying to create a multi-level data validation structure so that the second validation list is populated based on what the user selects in the first list. So for example, if the first field was "Company", and the second field was "Products", then I'd like the "Products" to populate based on the user's selection of Company.

The Company drop-down is straightforward as I just have a validation list "=CompanyList" with a defined named range.

For data integrity purposes it is essential that the names in the CompanyList remain the way they are, including spaces and special characters. So I cannot create a different named range for each company name since named ranges cannot contain spaces.

Basically I would like to be able to put something into the validation list formula such as "=VLOOKUP(Company,ProductTable,2)" but this would return only one value, whereas I need to return a list of values?

Many thanks in advance

Unfortunately I cannot use named ranges for the Company because the items in CompanyList contain spaces and special characters. These need to be maintained for data integrity purposes (the data is stored in a database and Company string must match exactly to populate the database).

Ah, I see. Perfect, thanks!

Hi,
So what happens if you have 1000 company names, let's call them customers. Would you be creating 1000 Name ranges?
Furthermore, what if you need to fill in the address1, address2, City, State, ZP, and country for the customer you have selected?
I just ask because I am trying to do the same thing: that is, select the customer (Bill-to info), with VLOOKUP, I am filling in the address information. Then From the selected "company" (I'll call it CUSTOMER) I need to select one of many SHIP-TO's and populate the address of that particular SHIP-TO record.
How would you do that?

Hello francisco, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.