An easy way to make fully dynamic related drop-down boxes

In previous blog posts, we have seen how to make drop-down boxes driven by the entries in other cells and drop-down boxes. In this blog, we will create a fully dynamic system where

• The data is dynamic; we can add more rows as we want by using a table

• The number of drop-down boxes that we can have is easy to expand

• The contents of the drop-down boxes are driven directly from the data set without adding additional named ranges.

To do this, we will use the spill functionality introduced in 2018 to make dynamic ranges for the contents of the drop-down boxes.

In the example below, we have a simple table containing the data and a set of drop-down boxes that are related left to right to each other. The user can select a Make from the list and then select the model and colour.

Dynamic dependent linked drop-down boxes driven by dynamic data

To set this up, first, enter the data and then press >CTRL><L> to make the data into a table

how to make a table

To set up the 1st set of values for the Make, enter the following formula in E5

=SORT(UNIQUE(Table1[Make]))

This makes a list of unique values in the Make column and then sorts them to make it easier for the user to find them.

Then go to E2 and set the data validation to “List” and the value to =$E$5#.

Data validation to produce the dynamic dependent drop-down

The # signifies that you are referencing a spilled range and that E5 is the top left of the range. Spilled ranges are designed to be dynamic, so it is good not to have anything in the cells below. If the result of the formula were to try to overwrite a non-empty cell, it would generate a #SPILL error.

To make the 1st of the dependent drop-down boxes enter the following formula into F5:

=SORT(UNIQUE(FILTER(Table1[Model],Table1[Make]=$E$2)))

This applies the criteria in E2 (i.e. the Make) to the Make column in the table and then returns the lines from the Model column. It then reviews this list, returns only the Unique items, and Sorts them to make entries easier to find.

The same data validation settings are used for F2, but referencing F5#

The 3rd related drop-down box is made in the same way with G5 =SORT(UNIQUE(FILTER(Table1[Colour],Table1[Model]=$F$2))) and the validation on G2 referencing =g5#

Note that the SORT(UNIQUE(FILTER functions can be positioned elsewhere, and if your data expands, you can have more related drop-down boxes with minimal effort.