Dynamic Dependent Data Validation lists are easy with dynamic array formulas, but in this video I show you both with and without dynamic arrays. Download the Excel file here: https://www.myonlinetraininghub.com/dynamic-dependent-data-validation

To dynamically expand the regions for more countries, copy the FILTER function across more columns and use the ‘not found’ argument in FILTER to return blank with two double quotes, e.g.:
=SORT(FILTER(TblRegions[Region],TblRegions[Country]=H4,/”/”))

Then modify the XLOOKUP to also include further columns e.g. if your FILTER formulas are in columns H:P your XLOOKUP would be:
=XLOOKUP($B5,$H$4:$P$4,$H$5:$P$5)#

0:26 Dependent Data Validation without dynamic arrays
1:28 Setting up Data Validation Lists
1:55 Setting up depeneden Data Validation Lists with INDIRECT
3:12 Dependent Data Validation with dynamic arrays
3:25 Distinct List for Primary Data Validaton List
3:56 Secondary Lists for dependent Data Validation Lists
5:20 Setting up depeneden Data Validation Lists with XLOOKUP

View my comprehensive courses: https://www.myonlinetraininghub.com/

Connect with me on LinkedIn: https://www.linkedin.com/in/myndatreacy/

Please take the opportunity to connect and share this video with your friends and family if you find it useful.