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

Question

Closed. This question is off-topic. It is not currently accepting answers.

---

Want to improve this question? Update the question so it's on-topic for Stack Overflow.

Closed 7 years ago.

Improve this 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

Answer 2

To make it work without VBA in Excel you'd need some way of resetting the range of valid entries for the data validation in Column 2 and since the source can only be a list or a range you'd have to code something to change that information when the associated Column 1 value changes.

If you don't want to go the VBA route then those kinds of activities are bread & butter for a database like Access (or maybe InfoPath) which would better suit your needs.