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

How would I employ the second method if I want to incorporate cities after regions and another layer of zipcodes related to cities?

I CANT USE THIS TACHNIC IN ONLINE V OF EXCEL

Great technique. appreciated. Thank you.

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

Very very very helpful! Many thanks :)

Thank you for saving an Excel Soul once again! haha!

I was looking for this tip. Thank you!

Amazing!

Thank you very much for this. Saved me lots of time!
I have a question. Is it possible to make the same for 3 rows?
for example to have a Country, Region, and City, where all 3 will be dependent one from another?
So basically the Region would depend on the Country, and the city would depend on the Region?
Thank you in advance!!

Amazing work! Thank you!!

Absolutely Awsome - thank you very much for sharing!

Hello, do you know a dependent data validation way that works on Excel on line?

Thank you so much, very simple and helpful

THANK YOU SO MUCH FOR THIS <3

Thank you for this video. Can you please help me one more thing.
Eg. When I select Australia i get drop down list in next column like you showed . But when I select UK i want user to write any value.
How can I do this. Pls advise

Well. Thank you. This video will be saved in the “Bananas” folder. 😊

I used some of these techniques recently.

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.

Hi you video was amazing. But I try the last part on Data Validation > input "=xlookup(......)# and it prompt me "The source currently evaluates tp am error. Do you want to continue?" Any advise for this? it would be much appreciated.

How to combine two rows in data validation list?

I tried this to filter at 3 levels but results in error. E.g, select country, then select states, then within states you select province. Any help?

Thanks for this was very helpful , one question is it possible using the 365 method to make the first list box searchable also

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.

why doesn't formula copy to other cells

Perfect, wondering if you head me in the right way, how to get rid of blanks while ticking on "ignore blank" in the validation window. I'm suffering from this.

Really helpfull and easy to understand, you have a new follower :-)

I do have a question on how to solve an issue, as I dont find this method truly dynamic, if I add a new country and corresponding regions.
The country gets added with no problem to the first dropdown, but I need to both copy the filter-formula used on the regions, AND change the xlookup-formula in the data validation to include the added columns (it seems like the # only applies to return_array's number of rows included).

Do you have a toturial for this specific case?

This is simply awesome!

Thanks Mynda for this very useful way of handling drop downs!!!

The doubt that I have is, using the example you give of Countries and Regions, what happens if the user goes back to a selection already done (eg: country=Australia and region=QLD) and changes the country to UK? That does not automatically blank the region, but leaves QLD which, if the user doesn't also select the region, will be a wrong combination... I'm not sure if there is an easy solution, not having to set controls that the input combination is the correct one...

Thanks and congratulations for your fantastic videos!!!

So useful for me right now, thanks a lot!!

Excellent video , yet how to copy the formula across ?

Hello again, I wrote you about 4 hours ago, and have been trying to get the dependent data validation working in my Check Register and Budget file. I am not having any success and do not know what the problem may be. I have made named ranges for all my Parent/Sub-Categories, and have made and named all as tables, including my main check register. When I try to make data validation using the names range “Names” I receive an error. Could I email you a sample of what I am doing?

I really like your video! I have a stand alone version of just Excel(Home & Student) I just purchased from Microsoft last month. I am trying to add the dependent data validation to an existing file. It is a checkbook register and budget, and the register has about 4500 rows and I have been using regular data validation. I am a little lost on what may be the best way to incorporate your 1st method into my file. I have 12 Parent Categories plus about 100 sub-categories in 2 separate columns.

Great video, i have one query when i am trying to have the 2nd drop down in another tab which is a table it is not working for me, kindly suggest

Great stuff very useful

Very nice

Another incredibly useful tutorial!! Thank you!!

Hi Mynda,
Thanks for the video, but unfortunately it's not working on excel online 365, although the xlookup function is there, so do you recommend any other way to have it on excel online

Thanks Mynda, is there a way for a user to add to the dependant list from the drop down without getting an error ?

You are one of the best teacher on excel

When I tried this excel kept transposing the columns when I dragged the formula over. For example I had =SORT(FILTER(FLLlist[Employee],FLLlist[FLL]=H2)) and hen I dragged it over the next column switched to =SORT(FILTER(FLLlist[FLL],FLLlist[Employee]=I2)) and then the third column would be correct, the 4th incorrect and it kept alternating for the 80+ columns I had. Why did it do that?

Hi thanks. What’s your recommendation if need to add third dependent drop down (eg. Region - country- city)?

Is it possible that filter function dynamically spell as more region is added. like we don't want to copy and past the column till very left. is it possible using array formula? thanks. that will make this process dynamic forever than.

I’ve been using the old method forever, even with 365 beta. I’ve saved this to download the example when I get off the phone and onto the keyboard! Just wondering if you’ve done any quick tutorials on using the “#” symbol? I get the basic idea but would love to see some more simple examples. Thanks!

Can you type to complete ?

Dashboards that I've built are using your videos. This video too will be useful. 👍