How to create DropDown which have dynamic Validation List

Question

I have data table with columns 'Category' and 'Product'. Product names are not unique, Category names are not unique, but the combination Category-Product appears just once in table. This data table is NOT sorted in any way.

Category  Product

========  =======

Chairs      Victorian

Chairs      Beautiful

Chairs      Edwardian

Chairs      Gross

Tables      Victorian

Tables      Edwardian

Tables      Huge

Tables      Kool

Tables      Lambda

Closets     Edwardian

Closets     Excellent

Closets     Major

Closets     Hello

Chairs      Huge

Tables      Picturesque

Closets     Picturesque

Chairs      Incredible

Closets     Minor

Chairs      Just

Chairs      Kool

I have already created temporary table with unique Category names, which will be used as validation range for the first dropdown list. This part works as it should.

Categories

==========

Chairs

Tables

Closets

Next to the first dropdown, I have another dropdown which should have dynamically created list of products of the category selected in previous dropdown.

If it is just for one pair of dropdowns (Category/Product), I can create satisfactory result using temporary columns.

Category:   Chairs           Product: Victorian

                                                  Beautiful

                                                  Edwardian

                                                  Gross

                                                  Huge

                                                  . . .

The problem is that this dropdown pairs should be part of another table with columns: 'Category', 'Product', 'Amount'. So, when user chooses in first column Category dropdown value 'Chairs', in the next column dropdown should be available in list just Products from category 'Chairs'. In the next row when the user chooses category 'Tables', in the adjacent cell should be available just products from category 'Tables'.

I am trying to make this using just formulas, array formulas, named functions (do not mix-up them with UDF functions) without VBA code.

Is it possible to do it or I am wasting my time (2 days already)?

Excel file with what I already did: here

UPDATE (2019-09-30):

Found this in a comment: 'However, Excel doesn't allow you to use array formulas in data validation,' ... by Fernando J. Rivera Nov 4 '17 on Excel dynamic drop down List by filtered table

So, it means that it is NOT possible to do dynamic data validation.

You are not wasting your time as it can be done in Excel but I was surprised that you spent two days without being able to find an answer online... @Pau has pointed the right direction. You just need to create a list for each sub categories and name them by the main category, and then follow the instructions to create the drop down list. — Terry W

Burd Burd · Accepted Answer · 2019-09-26T11:58:15

A way to tackle this problem is to use an IF function in the data validation window.

=IF($F$3=$B$2,$B$3:$B$10,$C$3:$C$10)

It checks if cell F3 of the category column and sees if it matches the string in cell B2. If it does, it uses the validation list under the column tables and if it doesn't it takes the other list. This could be extended to closets etc. by nesting IF statements.

Drawback is, as far as I can see now, the validation would have to be done for every row separately.

How to create DropDown which have dynamic Validation List

2 Answers