( see easier version in description )Excel Dependent Drop down - multiple rows AND multiple levels
Vložit
- čas přidán 24. 07. 2024
- See a much easier version in a newer video here: • The EASIEST Excel mult...
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 file aasolutions.sharepoint.com/:f...
Link to my simpler XLOOKUP single dependent drop down video
• XLOOKUP and easier Dep...
Link to Leila Gharani's video on multiple row single dependent drop down video
• Create Multiple Depend...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/
accessanalytic.com.au/ - Jak na to + styl
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?
Did you watch 14:20 onwards?
@@AccessAnalytic Sorted was just being an idiot with conditional formatting cheers mate
Ingenious way of doing it!! nice work and thanks for sharing!
You’re welcome
You are awesome. I had been looking for this for ages
Make sure you check out my easier / better version of this
You are magician of Excel 🎩 nice one
Thank you
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!
I'm curious about your solution Excelrobot. Can Wyn share if he does a follow up how to use other soltions for the same propose?
I’ll be taking a close look next week - approach looks interesting on first glance
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.
Hi, yes you can pass multiple criteria to XLOOKUP. E.g XLOOKUP( CellA & CellB & CellC, ColumnX & ColumnY & ColumnZ, ColumnG)
Check out my simpler approach in my new video…The EASIEST Excel multiple row drop down technique you've ever seen!
czcams.com/video/U3WnM2JCrVc/video.html
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.
Glad you got it working
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?
I’d check out the link in the description for my easier method
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.
Sure whopkins@accessanalytic.com.au
@@AccessAnalytic on its way!
I kind of missed the first minute, now I really see the problem. Got it. My "solution" doesn't do that. 😏
No worries 😀
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
Hi, it’s quite a niche combination so not something I’d likely do a video on. Thanks for the suggestion though
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
I do tend to say comments, but I mean Description. It can be quite hard to see but it’s underneath the videos
My brain hurts…
😆
Very poorly explained.. skipped so many steps..
You might prefer this simpler version I mentioned in the title czcams.com/video/5Z2OOriFxig/video.html
curious to know how he validating the results and color changes to red when previous value changes
I’ll be doing a MUCH simpler version of multi level validation in a video in the next 2 weeks, and in that video I’ll also show the conditional formatting rules
@@AccessAnalytic i tried transposing the columns and making matching values under each column .. and it worked
hope this helps refine your approach and comes up with a superb solution