Many things that were hard or complex are now much simpler. Creating dynamic drop down lists based on previous selections used to be tricky in Excel. Dynamic arrays make it straightforward.

Dynamic Arrays Reminder – this feature is currently only available in the subscription version of Excel. The file can be downloaded at the button at the bottom of the post.

I have three previous blog posts on this feature, plus three articles in the CPA Australia magazine – links below.

Blog Posts

https://a4accounting.com.au/new-formula-symbol-in-excel/

https://a4accounting.com.au/new-dynamic-array-function-sequence/

https://a4accounting.com.au/the-end-of-sumproduct/

CPA Australia Magazine Articles and Videos

https://www.intheblack.com/articles/2020/05/01/dealing-with-dynamic-arrays-in-excel

https://www.intheblack.com/articles/2020/06/01/dynamic-arrays-in-excel

https://www.intheblack.com/articles/2020/07/01/dynamic-arrays-sorting-lists-in-excel

Table

We will use this formatted table to create our drop downs.

The structure we will use in shown below.

There are only five formulas needed for this solution. Let’s see how the drop downs work before we look at the formulas.

In the image below we can choose a Category from the drop down in cell F2. This is based on the list in column J.

Once a Category is chosen a list is created in column K that is used in cell G2 for its drop down list.

After an Item is chosen in cell G2 the Size list is created in Column L which is used by the drop down in cell H2.

Watermelons only have Large and Medium sizes. So if we went back to the Item and changed it to an Apple and Small as below.

Then we changed the Item back to Watermelon like the image below it would be invalid.

We need to build in checks for these type of changes because the list updates, but the input cell can’t.

Formulas used

Note: the table is named tblProducts, as per top left in the first image on this page.

Note: formatted tables work well with dynamic arrays. Formatted tables automatically expand as data is added and this means the ranges used in the dynamic array also expands.

Let’s start with the easiest formula.

Cell J2

=UNIQUE(tblProducts[Category])

This extracts the unique entries from the Category column in the table.

Note the categories are repeated. For the drop lost we only want the unique entries.

Spilled ranges

The advantage of dynamic arrays is they automatically “spill” as required. There is no formula in cell  J3. The formula is in cell J2 and it “spills” down as far as required based on the number of unique entries in the Categories column.

Cell K2

=UNIQUE(FILTER(tblProducts[Item],tblProducts[Category]=F2,"none"))

This formula uses two functions in tandem. The FILTER function returns the list of Items when the Category matches the one chosen in cell F2. The “none” on the end instructs Excel to display the word none in the cell if nothing is returned by the filter. Nothing will be returned when cell F2 is blank. If you omit the error message on the end then the #CALC! error is displayed.

The FILTER function will return duplicates. The UNIQUE function will list the unique entries from the list that FILTER returns.

Cell L2

=FILTER(tblProducts[Size],tblProducts[Item]=G2,"none")

This is a similar formula to cell J2 but without the UNIQUE function. It is returning a list from the Size column based on the Item chosen in cell G2. We don’t need the UNIQUE function as the entries are already unique. Each item doesn’t repeat sizes.

Error checks

As we saw in the images at the top of the post it is possible to have invalid entries display. The formulas below below check for invalid entries.

Cell G3

=IF(OR(G2="",COUNTIF(K2#,G2)>0),"OK","Error")

This checks the entry cell G2. If it is either blank or the entry is in the list in column K then OK is displayed. The COUNTIF function is an easy way to determine if an entry is in a range. It does a conditional count. If the entry is there it returns 1 or more. If COUNTIF returns zero then the entry isn’t in the range. In our case that means we have a problem.

Note how the list in column K is referred to. By using K2# it refers to the spilled range created by the formula in cell K2. This range is dynamic and matches the size of the spilled range created by the formula in cell K2.

Cell H2

=IF(OR(H2="",COUNTIF(L2#,H2)>0),"OK","Error")

This is exactly the same structure as the formula in cell G3 but it is checking the entry in cell H2 against the list in column L.

Drop downs

The Data Validation dialogs for the cells F2, G2 and H2 are shown in the image below. Again note how the spilled ranges are referred to. This makes the ranges dynamic. You always refer to the top left cell of the spilled range. The cell that has the formula in it.

 

Getting sorted

There is also a new SORT function. So if you wanted to sort the drop down lists you can add the SORT function to the listing formulas.

Cell J2

=SORT(UNIQUE(tblProducts[Category]))

Cell K2

=SORT(UNIQUE(FILTER(tblProducts[Item],tblProducts[Category]=F2,"none")))

Cell L2

=SORT(FILTER(tblProducts[Size],tblProducts[Item]=G2,"none"))

Time to play

Dynamic arrays are a game changer in Excel. If you are lucky enough to have them then the earlier you start playing with them the earlier you will improve your skills.

Note it will be a while before most users have this feature, so be careful sharing files using these features and functions.

Download example file