MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Dependent Validation using Dynamic Arrays


October 07, 2020 - by Bill Jelen

Excel Dependent Validation Using Dynamic Arrays. Photo Credit: Siebe Warmoeskerken at Unsplash.com

The Data Validation feature lets you choose from a dropdown list in Excel. It works great until someone wants to have two lists. The items in the second list are dependent on what is chosen in the first list. This is called dependent validation.


In the figure below, the items for the first dropdown list appear in D4#, thanks to =SORT(UNIQUE(B4:B23)). The validation in H3 points to =D4#. The list for the second validation appears in E4# because of the formula =SORT(FILTER(A4:A23,B4:B23=H3,"Choose Class First")). The validation in H5 uses =E4#.

The product database in A4:B23 lists products and classes. A UNIQUE formula in D4 gets the UNIQUE of class. The person using the worksheet choose from a validation dropdown in H3. The source of that first validation uses the Spiller notation of =D4#. Once they have chosed a Class, then E4 gets a list of matching products with =SORT(FILTER(A4:A23,B4:B23=H3,"Choose Class First")). A second validation drop-down for Product is in H5. The source for that list is =E4#.

Title Photo: Siebe Warmoeskerken at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.