Excel dynamic drop down List by filtered table

Question

Hi I have a problem with the data validation list in Excel.

First of all I have one table(named: Article) in a protected Tab with four attributes (Name, Sex, Colour, Price). It is possible that two or more articles have the same name and differ by Sex (male or female) or by colour.

Name         | Sex       | Colour     | Price
-------------|-----------|------------|---------
Product1     | Male      | Blue       | 10
Product1     | Male      | Green      | 10
Product1     | Female    | Pink       | 5
Product2     | Male      | Grey       | 8
Product2     | Female    | Orange     | 8

In the main Tab I organized the order in an different table (called: Order).

Product      | Sex       | Colour     | Quantity   | Price
-------------|-----------|------------|------------|---------
<List>       | <List>    | <List>     |            | <Calculation>

The dropdown List for Product (Data Validation | List | Source -> =INDIREKT("Article[Name]"))

and Sex (Data Validation | List | Source -> Male; Female) is easy.

But how I get a list for Colour - depended on Productname and Sex?

For example Product = Product1 and Sex = Male the drop down for colour should contain Blue and Green.

Fernando J. Rivera Fernando J. Rivera · Accepted Answer · 2017-11-03T21:32:20

First you need to change the structure of your table. Either copy and transpose this with an array formula, entered as Ctrl+Shift+Enter:

=TRANSPOSE(TABLE)

You need your table in the following form:

Product 1 | Product2 | Product 3

Green     | Grey     | Pink

Blue      | Orange   | White

Then, you define each of those columns as a named range. You can select the entire table and go to Formulas > Create from selection (in the Defined Names section). Mark only "Top row" and click ok. Now you have named ranges you can use in your dependent or cascading dropdown list. In this one, your source will be:

=INDIRECT(A1) 'Replace A1 with the location of your first-level dropdownlist

Of course, it becomes considerably more complicated if you need to add an extra condition to your secondary dropdown. Firstly, your table needs to be in the following structure:

Product 1Male | Product1Female | Product 2Male | Product 2Female

Green         | Grey           | Pink          | Red

Blue          | Orange         | White         | Pink

And the formula for your dependent dropdown would be:

=INDIRECT(A1&A2) 'Where A1 and A2 are the locations of your other 2 dropdown lists.

That should work as you need it. Let me know if further clarification is needed.

Excel dynamic drop down List by filtered table

1 Answers