Easy Dynamic Dependent Data Validation Two Ways

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

[MyOnlineTrainingHub]

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)#

[rrrprogram]

You are one of the best teacher on excel

[mdallas]

As I mentioned in comments for your "Interactive Personal Finance Dashboard" you have an ability like no other to concisely explain the essentials of each part of Excel you present. No matter the user level of proficiency you make it so people can be up and running immediately. Keep up the great work and thanks for sharing it. These are the best instructional videos I've seen.

[syrah]

This was amazingly helpful! I don't have Xlookup in my 2016 version, BUT, because I was only needing a single list per drop-down, I was able to kind of 'waterfall' a functional method. You got me 99% of the way there with the Sort, Filter, Unique, and the Spill functions. I had never used them, so great work! Thanks for posting; love YouTube for this self-training type stuff.

[snicho]

Thanks Mynda!

I have previously used the Tables and Named Ranges for achieving this functionality; I wasn't aware of utilising Dynamic Arrays for this.

This is one that I'll definitely file in my "good stuff" collection for future reference! :)

[roisinclarke]

I love you so much. You have no idea how many videos I watched for days on end, I took a break and found you. Thank you SO much.

[CAKimberlyLewis]

Many thanks for including the formulas in the Description! SO SO SO helpful!

[mariaalcala]

Great video! I really like how you can easily explain everything!

[ekkiwurm]

Thank you Mynda for sharing this video. Really well explained.

[wayneedmondson]

Hi Mynda. Great tutorial! Thanks for showing both methods. Always nice to know multiple ways to solve the problem. Thumbs up!!

[IvanCortinas_ES]

As always an excellent material. Thank you Mynda. That's very important for the job.

[TMKJFun]

Thank you Mynda - This is very useful information, concise and well explained and I will be able to apply these concepts in many settings. I really appreciate these videos - they make Excel fun to use. I will definitely share this one with my Excel friends.

[patrickschardt]

You always explain things so well in your tutorials

[paulwarren]

Another incredibly useful tutorial!! Thank you!!

[utubeAgape]

Hmm, didn’t know about using a formula in the data validation source field- definitely going to play with that - thank you Mynda!

[cristoumanzor]

I was looking for this tip. Thank you!

[moisestatis]

THANKS SO MUCH!! very, very helpful. I was needed this.

[hizkiayosiepolimpung]

Great video. Far simpler than the other ones i looked at.

[Luciano_mp]

Useful, easy, i liked..Thanks Mynda!

[darrylmorgan]

Hi Mynda!Excellent Tutorial, Really Helpful...Thank You :)