Dependent Validation Using Arrays
October 18, 2018 - by Bill Jelen
Ever since Data Validation drop-down menus were added to Excel in 1997, people have been trying to work out a way to have the second drop-down menu change based on the selection in the first drop-down.
For example, if you choose Fruit in A2, the drop-down in A4 would offer Apple, Banana, Cherry. But if you choose Herbs from A2, the list in A4 would offer Anise, Basil, Cinnamon. There have been many solutions over the years. I've covered it at least twice in the MrExcel Podcast:
- The classic method used a lot of named ranges as shown in episode 383.
- Another method used OFFSET formulas in Episode 1606.
With the release of the new Dynamic Array formulas in Public Preview, the new FILTER function will give us another way to do Dependent Validation.
Say that this is your database of products:
Use a formula of
=SORT(UNIQUE(B4:B23)) in D4 to get a unique list of the classifications. This is a brand new type of formula. One formula in D4 returns many answers that will spill into many cells. To refer to the Spiller Range, you would use
=D4# instead of
Select a cell to hold the Data Validation menu. Choose Alt+D L to open Data Validation. Change Allow to "List". Specify
=D4# as the source of the list. Note that the Hashtag(#) is the Spiller - it means that you are referring to the whole Spiller Range.
The plan is that someone will choose a classification from the first drop-down menu. Then, a formula of
=FILTER(A4:A23,B4:B23=H3,"Choose Class First") in E4 will return all of the products in that category. Note that use of "Choose Class First" as the optional third argument. This will prevent a #VALUE! error from appearing.
There could be a different number of items in the list depending on the category selected. Setting up Data Validation pointing to
=E4# will expand or contract with the length of the list.
Download Excel File
To download the excel file: dependent-validation-using-arrays.xlsx
To learn more about Dynamic Arrays, check out Excel Dynamic Arrays Straight To The Point.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"Never delete an Excel file without backing it up first."
Title Photo: Félix Prado on Unsplash