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. 🙂
Girl i have been checking all these excel tutorials on youtube about drop-down lists for all rows, and no body even ever raised that this is an issue :")). Thank youu !
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!!
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,
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!
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.
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.
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.
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 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.
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!
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.
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)...
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.
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 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.
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!
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)
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
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 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..!
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!
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.
BRILLIANT !!! the ABSOLUTE BEST TUTORIAL on DEPENDANT & DYNAMIC DROP-DOWN Validation Lists, simple to follow instructions! Thank you, Thank you VERY much.
@@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.
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 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
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.
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" ?
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.
@@charkin9993I know this question is very old but I’m posting for anyone with this question now- I just made this work in my sheet by replicating the entire process over to the next column. So I had to rewatch the video a couple times 😂
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
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
😁 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.
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.
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!
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.
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.
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!!
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.
Yours excel study is excellent..with imagination and appropriateness to apply the formula.The thought of removing the space from drop-down list with applied COUNTA formula in particular validation in particular offset height,a copied list feels unbelievable.However going through tutorial and practically applying ,I had confused at the same point.Excel needs to explain - with his subjected language,ti find results...Good Tips and Tricks....Good Explanation.
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 very much , love u , I ve watched 100 s of videos , but I've failed , finally now completed my work at 2 pm because of you ❤ , greate thanks for your efforts ,
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 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.
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 😊
Grab the file I used in the video from here: pages.xelplus.com/multiple-dependent-dropdowns
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. 🙂
Girl i have been checking all these excel tutorials on youtube about drop-down lists for all rows, and no body even ever raised that this is an issue :")). Thank youu !
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.
You are our Excel National Treasure. Apreciate you so much. Sometimes these formulas are so mentally overwhelming.
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!
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!
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!
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!
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.
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.
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!
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
Legend Leila as always!
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.
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.
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!
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.
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)...
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.
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!
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.
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!
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!
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)
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!
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
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!
Excellent, never thought of this.
Thanks for sharing.
My pleasure. Hope it will come in handy for you.
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!
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!
Perfect. This is the one that I've been searching for months!!
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.
BRILLIANT !!! the ABSOLUTE BEST TUTORIAL on DEPENDANT & DYNAMIC DROP-DOWN Validation Lists, simple to follow instructions! Thank you, Thank you VERY much.
Wow, thanks!
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.
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.
Thanks for the great help. Instead of using COUNTA to restrict blank cells in list can't we use Ignore Blank option in validation
This is by far the best and most elegant solution I've seen on this. Thank you!
Wow, thanks!
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.
Thanks!
Many thanks Shane.
Love this function, thank you Leila. I'm going to play around with it.
Great. Let us know how it worked out.
WOW - Leila - Thank you - you are an amazing educator and have helped me immensely - all the best to you in your life
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 :(
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.
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" ?
Watched a few videos and this was the first that worked, thanks!
Glad to hear that!
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
@@charkin9993I know this question is very old but I’m posting for anyone with this question now- I just made this work in my sheet by replicating the entire process over to the next column. So I had to rewatch the video a couple times 😂
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
Your tutorials are easy to follow; Thank you for simplifying things.!!
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!
The problem with Validation is after I select the sub-section if I change the Division subsection remains earlier value. Can I reset 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!
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.
One of the best video on internet for hosting! ❤️❤️
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?
Thanks!
Thank you!
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!
Leila, Great Job. Your talent to explain every nitty -gritty things in a short time is very impressive. Keep up. Thank you!!!
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.
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.
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
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.
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
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!
name the ranges and use if statement in validation =if(a5="Productivity Div", ProdRng,if(a5="Game Div",GameRng,UtilRng))
then copy down
What an elegant solution to a very challenging problem!
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.
Omg, thats more than professional, and i thought i know Excel lol.
A very big thanks Leila
Glad you like it!
i admire you leila , yeah its great time every time to have your video on my all excel problems, Thanks Leila
Yours excel study is excellent..with imagination and appropriateness to apply the formula.The thought of removing the space from drop-down list with applied COUNTA formula in particular validation in particular offset height,a copied list feels unbelievable.However going through tutorial and practically applying ,I had confused at the same point.Excel needs to explain - with his subjected language,ti find results...Good Tips and Tricks....Good Explanation.
Very helpful, and now it's easy to grasp.
Many thanks. I enjoy how you educate and use your technique.
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!
Mulțumim!
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.
Just wonderful Leila, I already purchased one course and I am sure planning getting more...... GREAT !!!!! you sure are a 10 teacher
Thank you so much!
Nicely presented and clearly explained . Very informative and helpful. Thank you Leila
Glad you enjoyed it!
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.
WOW! Thank you so much. You saved my life. God bless you and more videos to come. 😍❤️🔥👏👏👏
Thank you very much , love u , I ve watched 100 s of videos , but I've failed , finally now completed my work at 2 pm because of you ❤ , greate thanks for your efforts ,
Glad it was helpful!
THE QUEEN OF EXCEL !!!!!! :)
Hi Leila, this video is just a gem!! NEver thought this could be done. Thanks very much!!!!
You don't know how grateful I am. I admire your diligence in making these videos. Thank you Leila 😍😍😍🙏🙏
My pleasure, Christine 😊
You are as elegant as ever. Very great way of teaching. Thank you
You are very welcome.
This is a very nice and straight through tutorial video that aids quick learning. Thanks to Leila
You're so good at explaining the formular. Thank you for taking your time.
You are so welcome!
Thanks, Leila, your teaching is amazing! step by step you explained everything. I subscribed and want to watch most of your videos. Very helpful!
I am looking for such a video on Dependent Drop Down List in Excel. It is excellent and very very informative. So I am in love with you Mam.
This is very helpful for me. Since i started my new position in my work, I always visit your channel for guide... thank you for sharing.
Happy to hear that!
i learn from you and make 3 and 4 multiple dependent drop-down list in same sheet
🥰🥰🥰
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. 🙂🙏🏻
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.
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.
Wow... indeed u r the best excel tutor ive ever seen... keep ur this great job and thank you 🙏🙏
You're a great teacher, thank you. This video helped.
Great tutorial. I enjoy learning new excel tricks!
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 😊
No way I would have figured this out without this video...thank you!!