Before we had the luxury of dynamic array functions, creating dependent data validation lists typically required using multiple tables and named ranges, as shown here. Setting them up was laborious, however now that we have dynamic array functions, creating dynamic dependent data validation lists is much easier.
Note: this technique requires a Microsoft 365 license for access to dynamic array functions. If you don’t have 365, you can use this technique.
Watch the Video
Enter your email address below to download the sample workbook.
Dynamic Dependent Data Validation with Dynamic Arrays
In the table below called TblRegions I have a list of my countries and some of their regions:
In cell H4 the UNIQUE function extracts a list of sorted country names which are transposed across the columns to form the headers for the region lists:
The result of the formula in cell H4 also feeds the data validation list for the countries. Notice the use of the # sign in the Data Validation List cell reference: $H$4# This ensures the data validation list will pick up any new countries added to the TblRegions table.
Note: you cannot use the FILTER function directly in the data validation list Source field because this field requires a range as opposed to the array of values returned by FILTER.
The data validation list uses the XLOOKUP function to return the range containing the list of regions for the country selected in column B. Notice the use of absolute and relative referencing on the lookup column ‘B’ and the use of the # sign to return a dynamic range (watch video for specific instructions):
With this approach any updates to the TblRegions will automatically be included in the data validation lists.