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/d...

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:   / myndatreacy