Excel 2020: Dependent Validation using Dynamic Arrays
October 07, 2020 - by Bill Jelen
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
Title Photo: Siebe Warmoeskerken at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.