( see easier version in description )Excel Dependent Drop down - multiple rows AND multiple levels

This is data validation at the next level.
If you have multiple rows needing data validation drop downs, and more than 1 dependent sub level to pick from then here's the technique

00:00 Intro
00:40 Illustration of the end result
01:20 The key concepts
05:25 The fancy next level bit - helper columns
14:20 Flagging invalid changes
16:00 Adding new items

Link to my simpler XLOOKUP single dependent drop down video

Link to Leila Gharani's video on multiple row single dependent drop down video

Did you know I've written a book "Power BI for the Excel Analyst"?

Connect with me

[darrenlincoln]

Great video mate, ive got a system working to 6 levels using your system. But cant find anything on how you have the invalid data highlight in red dynamically. Any ideas?

[adhossain]

You are awesome. I had been looking for this for ages

[marcossaraiva]

Ingenious way of doing it!! nice work and thanks for sharing!

[runningfun]

Thanks, I have done part of it . I needed to create a dropdown for grades of four different types of staff, each had about 12 grades of pay levels. making sure that the dropdown only showed pay scale and grades for one of the four types of staff. seemed to work.The only thing I had to keep the types of staff and pay rates on seperate sheets for it to work.

[Mohamed.GadAllah]

Thanks a lot for the video. I have a 6-column table and 15 levels in rows; do you think this technique would work well?

[darcyjohnson]

Excellent video! This was the exact result I was looking for on my form template so thank you. I do have one question though. Is it possible, based off this series of formulas to have a fourth column auto generate the result? In this instance it would be a dollar value. If someone selects a, b, c you would get d. If they chose b, c, a they would get e (as loose examples.) I've done it before with XLOOKUP but that was for a much more simplistic table selection.

[excelrobot]

This is very clever! Building off of what you have done, I thought I'd see if I could write a simplified version using dynamic arrays and LAMBDA functions. I think I got it! I'll send you a copy over LinkedIn. Let me know what you think!

[santoshkumarbehera]

curious to know how he validating the results and color changes to red when previous value changes

[joachimkober]

Hello, this is great
Question:
We have tons of data sheets in excel. So far they are done in separate Excelfiles and used like word
I like to read all those files in by power query and build a database out of it
Then I like to build the drop down lists automatically in power bi data flows with power query.
Out of that I like to build excel templates, where the multi level logic as you showed can be applied.
Would this be a topic for your video? Did anybody do something like this?
Thank you best regards, Joachim

[joachimkober]

I keep looking yiur videos since a while. Always great and i can kearn tonns from you and the community.
Sounds strange, but i never managed to find the links to download yout examples. This would help a lot
Can you or anybody help me how to do it?
I hear you alwasy saying take the link from the comments below
Do i need to buy you tube? I hope not.
Thank you in advance.,
Joachim

[sledgehammer-productions]

Hi Wyn, can I send you a version where the adding of new items is easier (except for clicking away an 'error' that doesn't seem to be a real error)? Only 1 tabel, number of named ranges equal to the number of levels.

[EthanEminence]

Very poorly explained.. skipped so many steps..