Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)
HTML-код
- Опубликовано: 12 июн 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Learn how you can create MULTIPLE dependent data validation lists in Excel on the SAME sheet using a single formula. This formula also excludes blank cells on the bottom of the data validation list.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/multiple-de...
👩🏫 Step-by-Step Tutorial:
Start with Basic Drop-Downs: Learn how to create a basic drop-down list for divisions.
Tackle the Challenge: Dive into creating a dependent drop-down list for apps based on the division selected, using the OFFSET formula.
Formula Magic: Discover how to dynamically change the drop-down contents using OFFSET and MATCH functions.
Avoid Empty Choices: Learn to dynamically adjust the number of choices in your drop-down to avoid empty selections.
Apply to Multiple Rows: Easily copy and paste the validation to multiple rows for a consistent, user-friendly experience.
🔧 Key Techniques Covered:
OFFSET Function: Understand the OFFSET function for dynamic range selection.
MATCH Function: Use MATCH to locate and select the correct column based on division.
Dynamic Counting: Implement COUNTA with OFFSET for dynamic list sizing.
In our example the selection of a division will drive the available choices for an app in an Excel data validation list. We'll use the Excel OFFSET function and the MATCH function for the dependent drop down selection. This way you can have as many categories as you like.
The challenge we have is that the number of choices inside the dependent drop down may vary depending on the division. I'll show you 2 solutions for this:
Fixed number of Choices in the dependent drop-down
If you do not mind empty spaces (blank cells) at the bottom of the drop down you can fix the height argument in the OFFSET function to a specific (maximum) number of choices.
Dynamic number of Choices in Drop-Down
If we want to restrict the number of visible cell values inside the dependent drop-down we can expand the formula to get rid of the blank cells on the bottom. This way we can eliminate empty spaces in the drop down selection. We will use another OFFSET formula inside a COUNTA formula to dynamically count the number of available choices.
00:00 Dependent Drop-Down Lists in Excel on EVERY Row
01:20 Creating the First Drop Down
01:58 Creating the Dependent Drop Down List
05:55 Fixed Number of Choices in the Dependent Drop-Down
07:15 Dynamic number of Choices in Drop-Down
10:21 Copy Down Data Validation
LINK to related OFFSET video: • Excel OFFSET Function ...
Complete Excel Data Validation Playlist: • Drop Down Lists in Exc...
➡️ Join this channel to get access to perks: / @leilagharani
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here: pages.xelplus.com/multiple-dependent-dropdowns
I'm always learning something new with these videos! Absolutely love LG!!!
I typically don't use the formulas presented, but I do incorporate small aspects of the formulas used; in this case, Counta.
Paying attention to the 'moving' parts of each formula can be a huge benefit in learning practical application and growth. I love the instruction given here! LOVE IT!
Amazing! Exactly what I was looking for to help with a project at work! So very well explained, very calm and soft voice to help understand complex tasks, very well informed and explains each step thorough. One of the best Excel tutorials I have ever seen. Will be looking through this channel for other tips! Thank you!
Perfect!! Thank you so much Leila! One thing I just learned the hard way is that COUNTA counts not only cells filled with contents but also with formulas, as well as null values ( "" ), after copy-pasting values to escape the formulas. The latter had me stumped awhile, with lots of extra blanks. A good "Clear All" beneath each of my subcategory lists got me sorted, and now I have beautiful drop-downs. Thanks again!!
That's great. Thanks for sharing!
Excellent explanation Leila. MULTIPLE dependent data validation function should be implemented in Excel as a embedded function (combined by simple one's). Very nice to watch your tutorials. :) Great job.
Thank you Leila ! As always, perfect !
This is total genius!
I literally followed step by step how you do it and i got it!
I love how you really explained stuff. Making complex thing simple. Thank you
Glad it helped Alvin!
I have found these videos are best watched with two screens and a hand on the pause button. It's slow only going 3 seconds at a time! Wow is this great stuff, though!
Hi Leila, thanks for having uploaded such a useful tutorial. You explain things in a crisp and clear manner. The combination of the formulas you showed works out like voodo magic! Please keep up the good work.
Thank you so much for this tutorial. Your explanation and step-by-step instructions are so easy to follow. I really appreciate your tutorials and look forward to learning new ways to help me do my job more efficiently. It's amazing that there are so many formulas that exist for the laborious and tedious steps we do manually.
Wow amazing mastery of the offset function ! And so clearly explained.
Thank you Lady
Glad you like my take on this Erick.
Legend Leila as always!
You are our Excel National Treasure. Apreciate you so much. Sometimes these formulas are so mentally overwhelming.
very cool. I'm still working through your essentials course while in lockdown. Stay safe
Great, it's good at always learning to improve yourself event though it's lookdown. Take care!
Thank you very much Leila for sharing all your great knowledge. I am actually quite surprised you don't use more (and emphasize) the use of Tables or at least named ranges since they are dynamic and avoid having to use COUNTA(). Furthermore, tables keep formatting and validation rules so no need to add unnecessary rules to empty cells.
Excellent explanation. Easy to follow and a life saver for me. Thank you!
I needed this solution for the project I’m working on. Awesome as always Leila! Thank you
This is my current problem, you have helped so much!
Love this function, thank you Leila. I'm going to play around with it.
Great. Let us know how it worked out.
Hi Leila, thank you so much for the video. They really help me to learn and apply to my work related projects.
Phenomenal usability and exactly what I'm looking for at the moment. Thank you so much.
You're very welcome Alex!
Hey Leila, I'm a fresh grad mechanical engineer and currently interning in oil and gas company, and my task is to create a database for my engineering department by compiling their previous and current projects, as well as gathering and combining documentations using Excel. Just wanted to thank you for your awesome video for the searchable drop down list (I also improvise it by assigning my macro in that data validation list and it will filter out all the unnecessary data), and eventually later in the future I would need to use this feature for the database. Your contents are truly great, and it allows me to think deeply on how to make my database even better using your video and other sources as references.
Hope you have a wonderful day!
Thank you for the kind feedback! I'm happy the tutorials are useful and you can use the knowledge at your work.
Excellent, never thought of this.
Thanks for sharing.
My pleasure. Hope it will come in handy for you.
Extraordinary! Exactly what I was looking for, very calm and soft voice to help understand complex tasks, very well informed and explains each step thorough. One of the best Excel tutorials I have ever seen.
Thank you for the video! You've widen my knowledge in excel!
Hey,
Apologies if this was mentioned before elsewhere but you can make this somewhat more dynamic, e.g., in the case you want to add a division.
The key is to format your division as a table, name it, e.g., TabDivision, and use indirect in the validation list:
Validation for the "Select Division" column: =indirect("TabDivision[#Headers]")
Validation for the first row (B5) in the "Select APP" column: =OFFSET(INDIRECT("TabDivision[[#Headers],[" & A5 & "]]"), 1, 0, COUNTA(INDIRECT("TabDivision[" & A5 & "]")))
That way you can easily add a division by adding a column to the table and the number of APP is changed automatically by the COUNTA...
There are 2 limitations:
- The APP in each division is a dense list, i.e., no null value except at the end.
- The name of the Table cannot be changed easily as excel will not propagate the change of name in INDIRECT
Cheers
Excellent! I love your alternative! However, it assumes there's no blanks in between the cells in any column. I think that's why Leila mentioned she preferred this technique. If your columns only have blanks at the bottom it works perfectly! Just thought I'd share that. 🙂
Your tutorials are easy to follow; Thank you for simplifying things.!!
Leila, Great Job. Your talent to explain every nitty -gritty things in a short time is very impressive. Keep up. Thank you!!!
Wow... indeed u r the best excel tutor ive ever seen... keep ur this great job and thank you 🙏🙏
This was a good topic to get me going. There are tons of tips in this one. During self-Isolation I’m going to binge watch and work on my Excel skills. Thanks for sharing.
Thanks.. this sharing wow n wonderful
I wonder why would someone dislike such an easily understandable educational video.
Haters gonna hate
I think some users aren't keen on using absolute cell references, and prefer using named range. Different strokes for different folks, I guess.
I like how clear you explained this.
Thanks Leila, just another perfect presentation, with easy to follow and understand steps. Your calm and clear voice just adds to the presentation value. Thanks again.
Thank you so much, Vladimir!
Fantastic! Very helpful. Would appreciate if you allow us to know the use of formula for the cases where 4 or 5 dependent columns are there.
Hi Leila! I love your vids cause of you I’m one of the best Excel users in my office. Question, wouldn’t it be easier and smarter to use the INDIRECT function alongside naming all of these dependant dropdowns with name manager? Just food for thought :)
You’re the best!
I was waiting for Leila to use indirect. Currently, somehow for work onedrive doesn't let me use offset so I use indirect (it's an auto save shared spreadsheet)...
LG, You are amazingly great! Very Informative formula and the trick. Thank you for sharing.....
Thanks, Leila, your teaching is amazing! step by step you explained everything. I subscribed and want to watch most of your videos. Very helpful!
Great method. A query after working around it:
After selection of an "app", can we have a dropdown option besides it containing list of values from two specified columns other than the one containing the "app" ?
For anyone having trouble with pasting the data validation and getting in other rows only the 1st option in the drop down list: after every MATCH COMMAND (there are 2 in the formula) do not lock the cell! So do not write in the formula MATCH($E$2...but leave that unlocked as MATCH(E2 ... and then this will work fine!
The way you teach these methods are digestible, that's what make your videos so will put. Learning from this channel has helped me understand more about Excel. Road to 500k will come sooner than expected. Thanks.
When it comes to any excel query, the only name comes in my mind is of YOURS. All praise is little.
Learnt a lot from you. Thank you Leila.
Thanks for the great help. Instead of using COUNTA to restrict blank cells in list can't we use Ignore Blank option in validation
LG: "Replace this 15 VERY CAREFULLY..."
Me: [flashback to all the times I used the direction key to get to another part of a formula] *single tear*
I Know... me too. UGH
I know that feeling! F2 is your friend here.
Leila Gharani I enjoy and have learned a lot from your videos; however, I’d like to suggest explaining why or how a user might want to use what you explaining or demonstrating.
@@LeilaGharani thanks. Tried this yesterday and mind was blown. No more accidental cells selected!
@@LeilaGharani F2? Wow.. I didn't know that. I'm going to try that soon.
YOU are my new favorite Excel Guru, thanks.
Excellent Leila, content and delivery. Thank you.
The problem with Validation is after I select the sub-section if I change the Division subsection remains earlier value. Can I reset it?
Great method ! Normally, I used different tables and I use INDIRECT Function =indirect(name of the table)
I was about to write this when i read your comment. This is exactly how i do it all the time. It is wayy simpler. 😁
What is the method? Any tutorial for it?
@@amgking7243 Hey! First drop-down is obvious, just as Leila did it. Before creating the second list, select the whole table (with headers, that is F4:H19), then Formulas Tab, Create from Selection, use the Top Row to create named ranges, Select Column B from B4 downward, then Data Tab, Data Validation, use List and type =indirect(A4) in the source cell. B4 is your active cell, that's why you need to reference A4. In case an error message pops up, click on continue. Indirect refers to now one cell to the left. To get rid of the empty cells, select the whole table again, hit F5 to open the Go to panel, click on Special, select Blanks. Right-click on one of the blank cells, choose Delete and Shift cells up. There you go.
I've used INDIRECT before. But, I don't like having to name all the ranges and the limitation of not using spaces within the names.
@@andyignacio6785 Indirect + substitute will resolve this issue. I only downside would be that you need to name each list.
Your explanations are super simple and easy to understand. Subscribed ✅
I love this video and saved it on my playlist. Thank you so much for this kind of value bombing content! I also like how you explained each step. More power!
thank you so much for your education video. How can we do this different sheet ?? pls help me for this. Exp: first sheet data, second sheet drop down list...
Yeah I'd like to know too! :D
Create named ranges for each list using the name in the column header (Productivity, utility, etc). Use the column headers for the data validation list asshown in the video. Data validation in the dependant cell uses the indirect function refering to the cell containing the column headers which are the same as the range names for the lists. Select the division in the first colum and the list for that division will be available in the second column. Copy to as many rows as needed.
I use this process too, is much easier for me.
Watched a few videos and this was the first that worked, thanks!
Glad to hear that!
Thank you so much, I am creating a simple address database for our Christmas cards and I want to use an Excel sheet as a database of CITY/STATE/COUNTY. This lesson is exactly what I needed.
how do I make 3 or 4 columns with multiple drop downs, each one dependent upon previous column. In this video the multiple columns are dependent upon one set of row.
Did u get a way to do that
I think you would just replicate the formula for each column, changing the parameters slightly each time to reflect the new data
@@charkin9993that’s exactly what I did as well
Am having problems copying the validation to the rest of the column as it keeps referencing to the first cell in the primary column, any help?
You don't know how grateful I am. I admire your diligence in making these videos. Thank you Leila 😍😍😍🙏🙏
My pleasure, Christine 😊
Great tutorial. I enjoy learning new excel tricks!
I need help mam. How to make multiple dropdown list which all should be interconnected with each other so that if we change a data from anyone of the dropdown then it should reflect in all like so if any changes in the other dropdown it should reflect in other dropdowns.
eg
Imagine a list of 4 columns and 4 respective dropdowns are prepared using data validation. If we select a value in dropdown 1 then it should reflect in the other dropdowns (2,3,4) automatically then if we change the value of dropdown 3 then the remaining dropdowns (1,2,4) should be changed.
please let this be the next video on coming Thursday, mam.
Thank you for your explanation. I used a named range for this and could make drop down lists with the INDIRECT() function. However, I would argue that the drop down list in this video is only a single depenent and not multiple dependant since the first list is independent.
To get a third row I created an additional table and named each column with "firstChoice_secondChoice". I, again, made named ranges of these columns for the third drop down list. This solution is not very elegant. Is there a better solution to this problem?
Can you explain better. I want same for me.
@@AnkitHadiyal My solution is only good for non-dynamic list. The idea is that you create a named list for every posible combination.
The first dropdown list is created by choosing a simple list. (i.e. {"Marketing", "Design", ... }
For the second dropdown list you create named ranges for each possible option. (i.e. select the options available for "Marketing" and go to "Formulas"->"Define Name" and use the name "Marketing" for the list.)
The dropdown list is created with the formula =INDIRECT( R a n g e o f d r o p d o w n 1)
For the third dropdown list you create list as in the second step but with names which combine the first and second input (i.e. Name the List "Marketing_London")
The dropdown list is created with the formula =INDIRECT(CONCAT( R a n g e o f d r o p d o w n 1, "_", R a n g e o f d r o p d o w n 2 )
(You may have to use SUBSTITUTE(text, " ", "_") as no spaces are alowed in named ranges.
Here is an example 1drv.ms/x/s!Asm2HYgDV9E_xkNKAATIcDoenbF8
@@AnkitHadiyal I hope you can use this as it is not dynamic.
@@TheGiers thanks, got it.
same problem for me, did you find a solution for a really multiple dependent drop down list?, i need at least 6 dependent children columns :(
i admire you leila , yeah its great time every time to have your video on my all excel problems, Thanks Leila
Your video tutorials are superb. Thanks. You’ve really helped grow my excel skills. It’s amazing what can be achieved in excel :)
Good one. I got a problem in pasting the data validation, when I paste the formula in other rows its only giving me 1st option in drop down list Such as If I select Productivitydiv its only give 1st option as Wencal only.
getting the same problem as you, did you manage to figure it out?
same here, any solutions yet?
after every MATCH COMMAND (there are 2 in the formula) do not lock the cell! So do not write in the formula MATCH($E$2...but leave that unlocked as MATCH(E2 ... and then this will work fine!
Hi Leila,
I have found that the second Combo box drop down list cn be easily populated using the following technique. Do you agree?
Define a name called Second_List whose RefersTo property is
=INDIRECT("List_"&Sheet1!$L$2)
and then use Second_List as the Listfillrange for the second combobox.
It has a problem that values being referred should not have a space in them
You are awesome Leila!
You really make it look simple!
Very helpful, and now it's easy to grasp.
Many thanks. I enjoy how you educate and use your technique.
Because offset is volatile Why not place countif(“*?”) in ur formula
or you could use =sumproduct(len(a5:a100)>0) that would skip formulae and only count results
name the ranges and use if statement in validation =if(a5="Productivity Div", ProdRng,if(a5="Game Div",GameRng,UtilRng))
then copy down
Simple, yet useful. Thank you so much for this video.
Really, your presentation is very clear and easily understood. Thank you very much!
Ive never seen a young woman so smart and beautiful
you clearly do not know many young women
This is a really incredible video. I thoroughly appreciate how you explain these things and make them comprehensible. Thank you!
Glad you enjoyed it, Brad!
Wow, this formula is amazing! Thank you!
Thank you for this! This was so easy to follow and gave me exactly what I needed!
😁 Fantastic! I watched this episode a few days ago but couldn't grasp it. However, when I needed to create multiple dependent dropdown lists, I revisited the episode, and wow, it has become much easier to understand.
Thank you very much from Saudi Arabia.
Glad it was helpful!
Great content and great explanation, thank you so much Leila!
i learn from you and make 3 and 4 multiple dependent drop-down list in same sheet
🥰🥰🥰
Always helpful. Thank you very much! You are the best!
Perfect. This is the one that I've been searching for months!!
thanks so much for this video! Very clear and to the point
Thanks so much Leila, clear and concise, rather than having to learn totally from the ground up. I use to think you must master all the different formula before apply nested formulas, now I just come to specific videos for specific solutions, save me tonnes of time.. love it..!
Glad it was helpful John!
Love your videos BTW! You have helped me with the biggest excel project ever! Noone explains it, like you!!!! Just finished and I have to thank you again!!
Glad I could help Amanda!
thank you so much for this clear and useful video
This was superbly awesome, mind blowing, short and simple
Simply a great approach I must say.
This was so helpful. Thank you!
Super useful! Many thanks!
Excellent tutorial. Thank you. Thumbs up.
Very insightful. Thanks for sharing
Perfect, just what I was looking for. worked like a charm 👏👏👏
One of the best video on internet for hosting! ❤️❤️
I spent hours trying to figure this out, till i came across your page. THANK YOU SO MUCH FOR THIS VIDEO. It was easy to understand and follow along.
Excellent walk through!
Excellent! Just what I was looking to accomplish! Thank you!
Wow....thanks...i learned so many things from your channel....thanks..
What an elegant solution to a very challenging problem!
Thanks so much! love your videos. I had no idea you could copy/paste validation. Used your tabular video to have multiple rows of validation using that formula combo. Cheers
WOW - Leila - Thank you - you are an amazing educator and have helped me immensely - all the best to you in your life
Excellent! It really helped me a lot. I can't thank you enough Ms Leila...
You're very welcome!
Amazing.. thanks I'll be using this one for sure
Thanks a lot for sharing this. It helped me amazingly!
Hi Leila, this video is just a gem!! NEver thought this could be done. Thanks very much!!!!