Complex dependent Excel drop down list

The aim of this tutorial is to create a dependent drop-down list that’s based on a messy Excel Table.

What do I mean by an unsorted Excel table?

The categories for the first drop-down are not in a tabular format and they are not grouped together. You can see an example of the data set below. In a previous tutorial I showed you how you can extract unique items for a data validation drop-down list that was based on a table which had multiple instances of each category.

In this tutorial I will take this one step further and create a dependent drop down list based on the same data set.

Great thing is: to get there we need to overcome a set of challenges.

excel find many matchesFirst: How to Find Many Matches

To get to our dependent data validation list, we need to be able to extract all records for each category (in the example, all apps belonging to each division). My approach is to use the INDEX together with AGGREGATE function in order to come up with a CSE (Control Shift Enter) free version of the formula.

Second: Absolute Column Table Referencing

The second challenge is to fix the column referencing in the table so that it’s fixed and doesn’t move as we drag the formula

Third: Create a Drop-down List that Excludes Empty Values

Here I use OFFSET together with COUNTIF and OFFSET again, to get a drop-down list that is restricted to the non-empty cells. I will be making a separate video on this in the coming months to explain the technique used in more detail.

AND finally in the end we get to our dependent drop down list :)

Watch the steps in this video:

Feel free to Download the Workbook HERE.

Free Excel Download

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!