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!
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. 🙂
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!
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!!
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
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!
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 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.
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!
Hi Leila, I solved this problem by using "name manager" and "IFS" formula and got same results into drop down list much more smaller&easier formula than OFFSET as follows: =IFS(A5=$F$4;Productivity_Div;A5=$G$4;Game_Div;A5=$H$4;Utility_Div) [It sould be used in Data Validation List section) That's all. However, I love the "OFFSET" formula because it is so miracle function. 🙌 Regards,
Hey Leila, I've bought your XELPLUS course Excel Essentials for the Real World and it's got incredible value for an extremely low price. It's just like your TH-cam videos - very clear and step by step. Little hint about your timestamps in the comments on this video: there are two sets of timestamps, which means chapters won't appear correctly when people want to jump ahead to the relevant section in the video.
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!
There was a mass lay-off that happened in our company last month. Fortunately I was retained and the reason for sure is that I am better at Excel now and Learned Power Query and Power BI (for the last 8months) that I was able to create Dashboards which are appreciated by the management. Thank you Leila, your videos helped me A LOT! Next up, I am trying to learn SQL so I can access our SAP Data. I think I am going to pursue a career as Data Analyst/Engineer. If you can read this Leila, can you advise me of where should I focus on and what other things I should learn to achieve my goal - I am an engineering graduate but I found data analytics as a fitting job for me as I enjoy it. Thanks again Leila, God Bless You!
It's great to hear you're dedicated to continuous learning. If you haven't already done so, I'd recommend diving more into DAX. It will help you create better reports in Power BI.
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.
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.
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 was so happy that I found your presentation yesterday. I don't use excel often and when I do, I don't need a lot of functions. But yesterday, I needed a formula that matched your example to a Tee. Your presentation was easy to understand, you broke down each component. Thank you.
@@LeilaGharani I have one remaining question. Using your example, if a user selected the productivity division and then choose a drop down from that list, but later decided he didn't mean to select productivity, but should have selected game division, is there a way that would for them to update the next column? My document has many small tables amounting to over 2000 rows amongst four worksheets. This formula is applied on 75% of the rows.
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)...
Leila, I watched several of your videos on Drop downs and finally solved my problem with your techniques. Much appreciated. I will review your courses and find more to learn from you. Thank you. Dave B
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.
You know what ? For every time, i'd be stuck and stubborn, i come here, read you 2, 3 times and boom ! i got my formula working, your teaching are close to a Jedi master. Bless u.
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.
@@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 agree with what Rudy Niel wrote above. I was about to write the same exact method when I read his post. I name the different lists and use INDIRECT to refer to those list. I find it way simpler.
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.
love your video another way to solve this system find create from the selection in the formula tab and hit it when you have selected all lists you have separately. then try to use data validation and use Indirect function to call all your selected names you have. process in steps 1- select your table one by one, then names based on top > formula> Create from the selection. repeat this for any column you have. 2- create data validation and write =indirect(first cell you have validation in it) Enjoy
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.
😁 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.
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!!
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.
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.
Leila, Thank you! Using the Offset formula was the most clever way to solve the dependent drop-down lists issue I was struggling with. I even used Xmatch nested in the formula. Awesome!
BRILLIANT !!! the ABSOLUTE BEST TUTORIAL on DEPENDANT & DYNAMIC DROP-DOWN Validation Lists, simple to follow instructions! Thank you, Thank you VERY much.
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" ?
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..!
thanks a lot, Leila for the great video. Double thumbs up. I took your idea and applied it to my problem. My data table was a list object. When selecting the ranges I noticed how Excel addresses the ranges. The formula did not contain the cell addresses as in your example, but the reference to the header of the list object: Data[#Headers]. I then tried to address the areas in the list object using the indirect function, as James Springer already suggested. I would like to share my solution because it solves the problem that I do not know how many rows are filled in each column of the list object. I used the following formulas for the drop-down lists: 1st drop-down: Data[#Headers] - If I add more columns, my drop-down list will expand automatically. 2. dropdown: OFFSET(INDIRECT("Data["& Cell-Adress first Dropdown &"]");;;COUNTA(INDIRECT("DATA["& Cell-Adress first Dropdown &"]")) - If rows are now added, the second drop-down list is automatically adjusted. Thanks again for your inspiration. It helped me a lot. Frank
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?
@@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
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...
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.
After some experimentation, I found one can achieve multiple dependent dropdowns much more simply using the INDIRECT() function. First, create a Drop-Down Table called DDTable with a Division column listing each division, a Productivity Div column listing each of its Apps, a Game Div column listing each of its Apps, and a Utility Div column listing each of its Apps. The entries in the Division column must match the other column headers to reference options correctly. For A8, enter the data validation formula '=INDIRECT("DDTable[Division]")' and for B8, enter the data validation formula '=INDIRECT("DDTable["&A8&"]")'. Extending the DDTable one row past its data will ensure every dropdown has a single empty dropdown option.
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.
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!
My brain is already full... I need an additional brain to feed all these. This is awesome Leila... you have always amazed. Thanks for this valuable tips and tricks.
Thank you for your great videos!! I have what I think a more robust / portable solution:you have 3 tables (Productivity,Game, Utility) Creat 3 named ranges with prefix "dv" (for example for data validation) and link them to that 3 tables (dvProductivity, dvGame, dv utility). In data validation of the second column put this formula "= Indirect( "dv" & $A5)" . Note that $A5 is the cell adjacent at the right, (inyour example) that has been selected by dropDown with the 3 tables titles... this way you can grow your tables dinamicalyyy. Thank you and keep UP the good Job
Excellent as usual and thank you for sharing this knowledge for free (yes some might complain it is not free ) but for me it is free as i prefer not paying $300-500/day to waste time learning excel basics while the course itself is called advanced excel. i only did this once and never did it again youtube and MOOCs (donate from time to time to support those organizations and it is well worth it) are my friends now.
Dear Madam, many many thanks from the bottom of my heart. It helped me a lot in managing database. Hats off to you. For such a brief yet very effective, detailed and nice presentation. Once again thank you very much.
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.
Not to worry Leila. I have figured it out, by using 'Defined Name Ranges" and can create dependent drop down lists which are located in other sheets. Thanks anyway, Your tutorials are awesome. Kind regards. Martin Argimon
Thank you soooo much I was having trouble using other alternatives since the dependent data was a number and everywhere they were using words which didn't work for me 😢 And you explained beautifully!! ❤
Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then filter the table's headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction.... happy to share my findings with you 😊
Thank you very much. I have learned many things from this video. Now i have able to do many things in the work environment. It's really helpful to those who are going to become an expert in advance excel.
Thanks for the tip, @LeilaGharani! Your TH-cam videos, as well as your Udemy courses, are always top-notch. What I was wondering, though, because this is something I needed a couple of months ago, is if there are any ways to prevent the dependent drop-down list from appearing whenever a specific value appears in the cell before. Example: A1 - B1 option 1 => dependant list 1 option 2 => dependant list 2 option 3 => dependant list 4 option 4 => user can type anything (no validation)
Hi, thanks for sharing! I personally do it using named ranges and using INDIRECT(“range name here”) under data validation to look up the desired range. It’s easier for me that way.
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
...this simplifies a few things at work! instead of custom spreadsheets for different sites with different work codes, this dependant drop down lets everyone us the same spreadsheet!
Hi Leila. Thank you for this. I was searching for "multiple items" selection in a drop-down list with VBA for work but came across this. This is also very helpful.
Thanks Leila, the formula is so helpful ! I used in a financial model in my work and it resolves a technical difficulty for the team. Love the way you explain. Thanks for much for sharing the knowledge!
![ออกพรรษาน้ำตาหล่น - แอน อรดี [OFFICIAL MV]](http://i.ytimg.com/vi/PFZtOcY9ONA/mqdefault.jpg)
Grab the file I used in the video from here: pages.xelplus.com/multiple-dependent-dropdowns
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!
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. 🙂
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.
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!
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!
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.
You are our Excel National Treasure. Apreciate you so much. Sometimes these formulas are so mentally overwhelming.
*Most fascinating video even Microsoft couldn't solve my issue thanks*
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.
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!
Hi Leila,
I solved this problem by using "name manager" and "IFS" formula and got same results into drop down list much more smaller&easier formula than OFFSET as follows:
=IFS(A5=$F$4;Productivity_Div;A5=$G$4;Game_Div;A5=$H$4;Utility_Div) [It sould be used in Data Validation List section)
That's all.
However, I love the "OFFSET" formula because it is so miracle function. 🙌
Regards,
Your IFS version is great too. Thanks for sharing!
Hey Leila, I've bought your XELPLUS course Excel Essentials for the Real World and it's got incredible value for an extremely low price. It's just like your TH-cam videos - very clear and step by step.
Little hint about your timestamps in the comments on this video: there are two sets of timestamps, which means chapters won't appear correctly when people want to jump ahead to the relevant section in the video.
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!
I was rewatching this and I have to say the dynamic arrays formulas really changed the game. Thank goodness.
They sure did. It really makes things easy now.
There was a mass lay-off that happened in our company last month. Fortunately I was retained and the reason for sure is that I am better at Excel now and Learned Power Query and Power BI (for the last 8months) that I was able to create Dashboards which are appreciated by the management. Thank you Leila, your videos helped me A LOT! Next up, I am trying to learn SQL so I can access our SAP Data. I think I am going to pursue a career as Data Analyst/Engineer. If you can read this Leila, can you advise me of where should I focus on and what other things I should learn to achieve my goal - I am an engineering graduate but I found data analytics as a fitting job for me as I enjoy it. Thanks again Leila, God Bless You!
It's great to hear you're dedicated to continuous learning. If you haven't already done so, I'd recommend diving more into DAX. It will help you create better reports in Power BI.
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.
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.
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.
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.
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!
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.
I was so happy that I found your presentation yesterday. I don't use excel often and when I do, I don't need a lot of functions. But yesterday, I needed a formula that matched your example to a Tee. Your presentation was easy to understand, you broke down each component. Thank you.
Glad it was helpful Catherine!
@@LeilaGharani I have one remaining question. Using your example, if a user selected the productivity division and then choose a drop down from that list, but later decided he didn't mean to select productivity, but should have selected game division, is there a way that would for them to update the next column? My document has many small tables amounting to over 2000 rows amongst four worksheets. This formula is applied on 75% of the rows.
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)...
Wow amazing mastery of the offset function ! And so clearly explained.
Thank you Lady
Glad you like my take on this Erick.
Leila, I watched several of your videos on Drop downs and finally solved my problem with your techniques. Much appreciated. I will review your courses and find more to learn from you.
Thank you. Dave B
Happy to help!
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!
Legend Leila as always!
Excellent, never thought of this.
Thanks for sharing.
My pleasure. Hope it will come in handy for you.
had an issue copying the formula to other cells, i have watched a lot of videos on this and you the only person who explained how to do this correctly
You know what ? For every time, i'd be stuck and stubborn, i come here, read you 2, 3 times and boom ! i got my formula working, your teaching are close to a Jedi master. Bless u.
Glad I could help!
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.
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.
I agree with what Rudy Niel wrote above. I was about to write the same exact method when I read his post. I name the different lists and use INDIRECT to refer to those list. I find it way simpler.
This is by far the best and most elegant solution I've seen on this. Thank you!
Wow, thanks!
Your tutorials are easy to follow; Thank you for simplifying things.!!
This is my current problem, you have helped so much!
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.
love your video
another way to solve this system
find create from the selection in the formula tab and hit it when you have selected all lists you have separately. then try to use data validation and use Indirect function to call all your selected names you have.
process in steps
1- select your table one by one, then names based on top > formula> Create from the selection.
repeat this for any column you have.
2- create data validation and write =indirect(first cell you have validation in it)
Enjoy
Love this function, thank you Leila. I'm going to play around with it.
Great. Let us know how it worked out.
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
It works! my data validation tore a couple of times on my Mac, but I finally got it when I was more careful to paste over "15"
That's good to hear. I don't have a Mac in the office so I never know if these things work on it.
😁 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!
Thank you Leila ! As always, perfect !
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.
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!
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.
The problem with Validation is after I select the sub-section if I change the Division subsection remains earlier value. Can I reset it?
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.
Leila, Thank you! Using the Offset formula was the most clever way to solve the dependent drop-down lists issue I was struggling with. I even used Xmatch nested in the formula. Awesome!
Glad it was helpful!
BRILLIANT !!! the ABSOLUTE BEST TUTORIAL on DEPENDANT & DYNAMIC DROP-DOWN Validation Lists, simple to follow instructions! Thank you, Thank you VERY much.
Wow, thanks!
Thanks for the great help. Instead of using COUNTA to restrict blank cells in list can't we use Ignore Blank option in validation
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" ?
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!
thanks a lot, Leila for the great video. Double thumbs up.
I took your idea and applied it to my problem. My data table was a list object. When selecting the ranges I noticed how Excel addresses the ranges. The formula did not contain the cell addresses as in your example, but the reference to the header of the list object: Data[#Headers].
I then tried to address the areas in the list object using the indirect function, as James Springer already suggested. I would like to share my solution because it solves the problem that I do not know how many rows are filled in each column of the list object.
I used the following formulas for the drop-down lists:
1st drop-down: Data[#Headers] - If I add more columns, my drop-down list will expand automatically.
2. dropdown: OFFSET(INDIRECT("Data["& Cell-Adress first Dropdown &"]");;;COUNTA(INDIRECT("DATA["& Cell-Adress first Dropdown &"]"))
- If rows are now added, the second drop-down list is automatically adjusted.
Thanks again for your inspiration. It helped me a lot.
Frank
Thanks for sharing your solution, Frank!
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 :(
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
You don't know how grateful I am. I admire your diligence in making these videos. Thank you Leila 😍😍😍🙏🙏
My pleasure, Christine 😊
Another great one. I tried several other tutorials from other people and none worked for me until I tried your solution. Thank you :)
Great to hear!
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
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
After some experimentation, I found one can achieve multiple dependent dropdowns much more simply using the INDIRECT() function. First, create a Drop-Down Table called DDTable with a Division column listing each division, a Productivity Div column listing each of its Apps, a Game Div column listing each of its Apps, and a Utility Div column listing each of its Apps. The entries in the Division column must match the other column headers to reference options correctly. For A8, enter the data validation formula '=INDIRECT("DDTable[Division]")' and for B8, enter the data validation formula '=INDIRECT("DDTable["&A8&"]")'. Extending the DDTable one row past its data will ensure every dropdown has a single empty dropdown option.
Leila, Great Job. Your talent to explain every nitty -gritty things in a short time is very impressive. Keep up. Thank you!!!
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?
name the ranges and use if statement in validation =if(a5="Productivity Div", ProdRng,if(a5="Game Div",GameRng,UtilRng))
then copy down
Been looking for this for some time. Previously I'd done same thing but it's read the empty cells. Now i knw hw to fix it. Thx alot Leila. 👍😄
My pleasure. Hope it will come in handy for you.
Watched a few videos and this was the first that worked, thanks!
Glad to hear that!
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!
Ive never seen a young woman so smart and beautiful
you clearly do not know many young women
Your channel is one if the few that i keep coming back to watch several times the same videos because they are just so helpful. And me so forgetful 😅.
Awesome, thank you!
YOU are my new favorite Excel Guru, thanks.
My brain is already full... I need an additional brain to feed all these. This is awesome Leila... you have always amazed. Thanks for this valuable tips and tricks.
Ive watched maybe 15 videos about it, but Ive found the exact solution only in this video. Thank you very much!!!
Glad it was helpful!
Thank you for your great videos!! I have what I think a more robust / portable solution:you have 3 tables (Productivity,Game, Utility) Creat 3 named ranges with prefix "dv" (for example for data validation) and link them to that 3 tables (dvProductivity, dvGame, dv utility). In data validation of the second column put this formula "= Indirect( "dv" & $A5)" . Note that $A5 is the cell adjacent at the right, (inyour example) that has been selected by dropDown with the 3 tables titles... this way you can grow your tables dinamicalyyy. Thank you and keep UP the good Job
Wow... indeed u r the best excel tutor ive ever seen... keep ur this great job and thank you 🙏🙏
Excellent as usual and thank you for sharing this knowledge for free (yes some might complain it is not free ) but for me it is free as i prefer not paying $300-500/day to waste time learning excel basics while the course itself is called advanced excel. i only did this once and never did it again youtube and MOOCs (donate from time to time to support those organizations and it is well worth it) are my friends now.
Omg, thats more than professional, and i thought i know Excel lol.
A very big thanks Leila
Glad you like it!
I needed this solution for the project I’m working on. Awesome as always Leila! Thank you
Dear Madam, many many thanks from the bottom of my heart. It helped me a lot in managing database. Hats off to you. For such a brief yet very effective, detailed and nice presentation. Once again thank you very much.
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.
Not to worry Leila. I have figured it out, by using 'Defined Name Ranges" and can create dependent drop down lists which are located in other sheets. Thanks anyway, Your tutorials are awesome. Kind regards. Martin Argimon
All your videos are superb. So kuch valuable information you share with us, thank you so much. Keep sharing, keep educating us. Love from India. 🙂🙏🏻
WOW - Leila - Thank you - you are an amazing educator and have helped me immensely - all the best to you in your life
Thank youuuuuuuu ! Saved my life. I was tried of finding a way for specific selection. This was on point. Thanks :)
You're welcome!
Your Channel @Leila is th first thing comes to mind when facing an issue with Microsoft excel. Keep up the good work!
That's great. I'm glad you find the videos useful.
Thank you soooo much
I was having trouble using other alternatives since the dependent data was a number and everywhere they were using words which didn't work for me 😢
And you explained beautifully!! ❤
Perfect. This is the one that I've been searching for months!!
What an elegant solution to a very challenging problem!
i learn from you and make 3 and 4 multiple dependent drop-down list in same sheet
🥰🥰🥰
Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then filter the table's headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction....
happy to share my findings with you 😊
Thank you very much. I have learned many things from this video. Now i have able to do many things in the work environment.
It's really helpful to those who are going to become an expert in advance excel.
Wonderful!
Thanks for the tip, @LeilaGharani! Your TH-cam videos, as well as your Udemy courses, are always top-notch.
What I was wondering, though, because this is something I needed a couple of months ago, is if there are any ways to prevent the dependent drop-down list from appearing whenever a specific value appears in the cell before.
Example:
A1 - B1
option 1 => dependant list 1
option 2 => dependant list 2
option 3 => dependant list 4
option 4 => user can type anything (no validation)
While this seemed way too complex at the beginning, once I started to write it for my worksheet it all made sense. Much appreciated
Glad it was helpful!
Hi, thanks for sharing! I personally do it using named ranges and using INDIRECT(“range name here”) under data validation to look up the desired range. It’s easier for me that way.
Indirect is a volatile function. If your data is large then, there will be delay in calculation of data after every cell value change.
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
...this simplifies a few things at work! instead of custom spreadsheets for different sites with different work codes, this dependant drop down lets everyone us the same spreadsheet!
Hi Leila. Thank you for this. I was searching for "multiple items" selection in a drop-down list with VBA for work but came across this. This is also very helpful.
Glad it was helpful!
Thanks Leila, the formula is so helpful ! I used in a financial model in my work and it resolves a technical difficulty for the team. Love the way you explain. Thanks for much for sharing the knowledge!
Glad it was helpful!
I have been searching for this solution for years ... you have no ideia... thank you so much 🙏🏻
My pleasure. I hope it will come in handy for you.