Dynamic Data Validation in Excel (Non-VBA!) [closed]

Question

I got n Categories which hold m Products. This list is unsorted, for example like this:

Now here's what I basically want to do with this list:

1. Dropdown: Data validation which dynamically stores a list of each unique value of the first column (= I won't be able to select Category_5 since it does not exist yet).

2. Dropdown: Provide a list of all products based on the selected category in the first Dropdown.

Is there any non-VBA solution for this?

Answer 1

Example spreadsheet layout:

• A2:A13 - Category list, the items are not unique
• B2:B13 - Product list
• Cell D2 down - Dropdown list 1 source, unique items, dynamic
• Cell F2 down - Dropdown list 2 item incremental number 1.. (less calculations in the formula)
• Cell G2 down - Dropdown list 2 source, dynamic
• Cell I2 - Dropdown cell 1
• Cell J2 - Dropdown cell 2

Cell D2: array formula (Ctrl-Shift-Enter from formula window to enter, curled brackets are inserted by Excel, not by a user), copied downwards:

{=INDEX($A$2:$A$13;MATCH(0;COUNTIF($D$1:D1;$A$2:$A$13);0))}

Gives a unique list of categories.

Cell G2: array formula, follow instructions in the previous section:

{=INDEX($B:$B;SMALL(IF($A$2:$A$13=$I$2;ROW($A$2:$A$13);"");$F2))} 

Gives a list of products under a category selected in cell I2.

The lists are not sorted: better to sort the initial lists by category and product.
No error handling: the formulae should be updated with IF ISERROR clause.

Dynamic_dropdown_lists.xls