Multiple Cascading Dropdowns

Hi All,

I have an issue with cascading dropdowns.

I have the following data. Entity = UK, GMBH, JAPAN, SING

I have then got lists of products, suppliers and customers specific to those entities.

So i can use the =INDIRECT(A2) to populate the first dropdown i have set up which are products which are called UK, GMBH, JAPAN, SING, but i also want to have it populate the next two columns which are the suppliers and customers too.

They are not dependant on the choice of product so i can't use the =INDIRECT(SUBSTITUTE) function as the two further dropdowns are only dependant on the Entity on the first choice.

But I can't name all 3 dropdowns with one name as it won't know what to chose. Can i have a function along the lines of =INDIRECT(A2,UK_Suppliers) where it will then pick the list i have called UK_Suppliers.

If anyone could help i would be most grateful, i have a menacing hoard of directors bearing down on me to get this done!!

Thanks in advance

I think that INDEX is a better option.

Please review the attached workbook.

Thanks Whizbang but i don't think this will work for what i am trying to do.

I need to select from a dropdown the country i want to view in column A, then it populates dropdowns in columns B, C & D with the Products, Customers and Suppliers applicable to that country in A.

Does that make sense? I am really bad at explaining these things, but i suppose the best way of describing it would be i want 3 seperate dropdowns in three seperate columns dependant on my choice of country in column A.

As i can only name one field UK (for example) i can't use the =INDIRECT function as this only returns one field of data.

Can you post a sample of your workbook so we can wip something up?

I have just cut a snippet out of my spreadsheet so you can see what i mean.

My attached workbook will do what you say. It is just a matter of adding in the two extra named ranges and modifying the formulas to search the added tables.

See the attached.

That works!! Brilliant thank you.

This has however thrown up another problem. I have a VLookup on the sheet looking at the customer name and populating the code next to it. This worked fine when it was one entity per spreadsheet but now i have combined them onto one with the INDEX for the data my lookup doesn't work.

Can i have a lookup that looks for the entity too and picks from the appropriate list?

Thank you