Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells
VloĆŸit
- Äas pĆidĂĄn 24. 07. 2024
- Join 400,000+ professionals in our courses here đ link.xelplus.com/yt-d-all-cou...
Dive into the world of Excel with our guide on creating dynamic and smart dependent drop-down lists! This tutorial tackles two common challenges: expanding lists automatically with new data and ensuring no empty fields in your lists. Ideal for both beginners and advanced users, this video offers two methods: using the OFFSET formula and table referencing.
âŹïž Download the workbook here: pages.xelplus.com/dynamic-dro...
â What You'll Learn:
âȘïž The basics of creating main and dependent drop-down lists in Excel.
âȘïž Using the OFFSET formula for dynamic list adjustments.
âȘïž Implementing table referencing for efficient data management.
âȘïž Techniques to ensure your lists automatically update with new entries.
âȘïž Solving the problem of varying list lengths in dependent drop-downs.
Create drop-down lists in Excel that are dynamic - #1 They expand as new data is added to the list, #2, they are dependent on another list and #3, they exclude blank/empty cells on the bottom of each list. Basically they are only restricted to the cells that have values in them.
In this video you'll learn how to use the OFFSET function to get fully dynamic dependent drop down lists.
You'll then see another version which uses INDEX & MATCH as well as Excel Tables. We plug in the formula we create inside Name Manager and then use the new name inside our custom data validation formula box.
â Links to related videos: â
Data Validation drop down Playlist: âą Drop Down Lists in Exc...
Learn OFFSET: âą Excel OFFSET Function ...
Learn INDEX & Match: âą How to use Excel Index...
âĄïž Join this channel to get access to perks: / @leilagharani
đâ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
đ Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
đ„ RESOURCES I recommend: www.xelplus.com/resources/
đ©Letâs connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
Grab the file I used in the video from here đ pages.xelplus.com/dynamic-dropdown-file
Hello mam, I am needing to have a blank cell for selection in the dependent drop down but I cannot make it work, is this doable? I am able to make it happen on the initial drop down, just not the dependent one. Any suggestions or can you help me put that together??? Thank you, Carl
Also, can the dependent drop down be tied to another workbook such as a room roster whereby the room is vacant evidenced by no name being in the occupant name cell for the room.
Leila, since I have discovered your videos, you have been my go to for ALL excel related questions. There are countless videos out there but what sets you apart is the way you explain it! Thank you for posting the amazing videos....
I love excel and I've been known to be able to create some nice sheets and simple workflows but seeing your skills. I feel like such a novice but in a good way. I am extremely impressed with your tone, your explanations and you examples. You are awesome. You've gained a subscriber today. I hope you gain many many more!
Thanks Leila! two thumbs up for both methods, I find the table method far more intuitive and easier to recall and reuse.
Totally agree... In fact, I just used the table method right now
@@utibe007 1%qqqqq%qqqqqq%
Alt + D + L will bring up the data validation details/window for your selected cells... super helpful when doing lots of these and playing around with ideas đđŒ
This is incredibly complicated. Its amazing to me how the human brain's intuition can quickly deduce information effortlessly compared to the difficulty in passing on that problem solving ability to a sophisticated program like Excel.
Thank you so much for starting with a hard example because obviously people that have to look up tutorials know what they're doing and are obviously already fluent with the match function
I was actually looking 3rd part of the video (deal with table)for my solution, hats off to you again.
Love it an understatement in the feeling I have toward your Excel knowleage. Thank-you so much, you explain these concepts better than a paid course.
You are a lifesaver! I used the offset method in creating drop-down list with 198 tables as reference and it worked like magic! Thank you Leila! â€ïž
I'm glad to hear it worked out Jim.
I used this the other day and used the table version, thank you!!! Using tables for the list and creating namesd ranges made running totals and remaining volume easy.
fantastic! I'll be using this indispensable information immediately. ..you're the best Leila
Love this video! My go to Excel help. I prefer the table method since it's dynamic and I can sort each table independently.
I cant thank you enough , have been trying for ages going thru several youtube videos , and you finally solved my problem......thank you....thank you
Any excel lover who love excel will not resist subscribing your channel.
Cool stuff Leila. Thank you very much
Hi, I found the table formula more convenient as it directly adds any new addition to the list automatically and hence negates the complexity of adding another offset formula. However, the whole idea itself was terrific. Thank you very much
Thanks so much for going over multiple methods! The Offset method worked best for my purposes, great tutorial đ
Your videos are the best, clearest, easiest to understand Excel instruction I have seen, and I strongly recommend them to my friends.
Suggestion for your dependent dynamic dropdowns -- the top level list across the top row can be dynamic, too, like the second level lists down the columns.
I found a way to extend dependent lists beyond two levels, to an indefinite number of levels. I call it indefinitely extensible dependent dynamic drop downs. The approach uses OFFSET/MATCH/COUNTA similar to your video, but all the lists regardless of level are in rows, in no particular order. I chose this arrangement because there could be many different lists (suitable for rows), but each list should not contain many choices (suitable for columns). The first column of each row contains the sequence of higher level selections that lead to this list (for readability I separate the selections with period, like a URL). The MATCH searches in the first column for a row that contains exactly the currently selected sequence of higher level choices, and nothing more. The number of levels can vary -- maybe one sequence goes to 5 levels, but some others go to only 1 (no dependent list) or 2 (one dependent list per level 1 selection). No problem. When you reach the end of the line (no further levels), the MATCH comes up empty, OFFSET returns a null list, and the dropdown list is empty so you know there is no further selection to be made. I find this approach to be extremely flexible, compact in worksheet real estate, and no more difficult to implement than the approach in your video. Please let me know if you would like more details, tbg.mail@verizon.net.
You are a VERY good teacher. This is the fourth video on the topic that I have watched and I found your video the easiest to understand and retain thanks to your clear instructions!
Thank you for the kind feedback.
you do the best excel tutorials online, fact. Just wish you did a few more about macros though :(
Putting a formula in the Name Manager for use in Data Validation... brilliant!
OMG your amazing!! This has just solved a problem Iâve been working on for hours!! I was using dynamic validation using tables but needed a shared document!! Obviously you canât share a document with tables so this has cured my headache, thank you so much đ
I'm glad this helped! Thanks for the feedback.
I'm using Excel every day but when i see your lessons for Excel i love Excel too much. â„ïž
Wow - you taught me a bunch in 16 minutes! Thank YOU!!!
Thank you for this! The table solution saved me.
Hey your tutorials are really helpful and made my day easy. I just want to know, can we use the offset solution with table format?
I know you get a lot of comments but I am in my sixties and these days struggle learning new things. it can be so worrying. This video, as all your videos is the best available anywhere on CZcams. It gets me over that "what's wrong with me, why can't I get this" hump. Thank You.
Hi Leila, Yet another great video, clear and easy to follow. Thanks.
I'm testing the functionality now on one of my workbooks.
I'm glad to hear that Karen. It's great you can apply it right away.
Congratulations and many thanks -- your tutorials are very clear, very informative and useful. The graphics are uniquely impressive as they blend in very well with the "how to" aspect of the topic. More power to you!
Many thanks for the kind feedback!
Your videos are amazing Leila, this helped me a lot!
I have been watching all your videos even your new ones. This is very helpful! Thank you so much! Hope you keep on posting new vids! :)
Many thanks for the feedback! New video coming each week :)
Ma'am, Excellent Tutor you are. Thanks a lot. Your solution saved my time through the 1st method. I have to modify it very little and working in my worksheet effortlessly. Be Safe and Healthy, and happy also.
A huge thank you for your videos. Great tutorials that have really helped my own business.
Hey, Leila
Thanks for such wonderful technique , as usual you are awesome again...I like 1st method ..Keep sharing ..Very Nice
You have solved my lot of problems leila, thanks a lot..
Wow! I have attempted the offset function and it worked out well. Thank you!!!!
Hi Leila, i love your videos, you're so sharp! im also taking your vba courses on udemy, thank you for sharing your knowledge with us.
Mind blowing functioning by Leila. Learnt a lot from this clip. Weldone...
Great video! Very useful and easy to follow. Thanks a ton!
Thanks a lot Leila, for your wonderful contribution to future. Thanks again
Thanks a lot Liela. I watch your lessons with great interest and have learnt a lot from your videos. Could you please provide downloadable files as you explain in your videos. That would really be helpful to understand the complexed formulas.
This was soooooooo helpful for me and was exactly what I needed. You are thorough and the on-screen prompts are very handy. Thank you!
You're very welcome, Wendy!
Very useful and helping me alot to understand it... thank you so much
Dear madam a another series of your amazing videos
Really it is eye opening
Leila, congratulations! I am a financial analyst and like your course as well as excel. Your town Vienna is a beautiful city. I wish you all the best with your new plan!
Many thanks for your support!
I like your way of explaining which can be understood well by all audiences.đ
Your Excel videos are the best! Very easy to understand! THANK YOU!
Glad to hear that!
I was aware only indirect function for creating the dependent drop down. Now I learned two more ways. Thank you so much for this amazing video.
You're very welcome. Glad you found something new here :)
Your channel is fantastic! Heep up the great work and thanks for sharing all these GREAT tips and tricks.
Thank You Leila, you have been very helpful!
Exactly what I needed right now! Very helpful!
Great Leila, this training was super helpful
The quality of your videos is amazing!!
I'm glad you like the videos Ismael!
I will go now for method 2 in my worksheet, but in cases where I need logical conditions that modify my dynamic list, I will go for method 1. I am very grateful that you have made this explanation very clear, useful, and free..so much that I am already a new subscriber. Thank you!
Great to have you here Alvaro :)
Great video, well presented as always. Many thanks.
Absolutely brilliant! I like the second method very much! Thank you for sharing, Leila :)
You're very welcome Malina. Thank you for dropping by :)
This is beautiful..... Exactly what I needed. Thanks Leila
You're very welcome. I'm glad you like it.
Leila, I've completed most of your Udemy courses and after these Drop Down tutorials must admit that you simply ROCK! Having you in my back pocket makes me feel confident that I can tackle any problem while knowing that you have my back, so to speak! Quite succinct and eye-opening is how I interpret your lecturing capabilities... In closing, don't, please don't... STOP!!!!!
Thank you Grant - that's really kind! I'm really glad you find the tutorials helpful for your work.
More than GREAT, very useful formula. Many thanks
Your voice is so very "comforting" - great video
Thank you. I'm glad to hear that :)
This was unique, amazing and simple.
Thank you :)
You're very welcome Deepak! Thanks for watching.
While you were explaining the offset function, I kept asking myself why weren't you just using tables and names for ranges in Name manager, and then you did. This is a quite informative and well-structured tutorial. *Thumbs up*
Thank you for the thumbs up!
Very useful tip, great explanation!
Thank you! Glad you like the video :)
Mind - BLOWN!
Perfect!!! You are AMAZING!
Very informative explanation as usual. Thanks
Love it. Very informative and easy to follow.
Thanks, very useful video to create a dependent list.
Whoa. That was great. Thanks!
Thanks for your support..
All of your videos are outstanding. Please keep up your great work. Thanx again.
Thank you for the kind words. I'm glad you like the videos.
Parabéns. Excelente video aula.
Thank you so much this is amazing!
Great solution...thank you
Exactly what I needed. Thank you đ
Thank you for these videos.
I find your videos very interesting and helpful. thumbs up for you.
Glad you like them!
lovely, i am impressed. you are wonderful. thank you
this has been a massive help in my analysis - thanks Leila :)
You're very welcome Phil!
Thank You very much
You are fantastic Leila
Thanks Leila. Very interesting and helpful. :)
Thanks John :)
Great video visual style and content as well. Thank you Leila
Thank you Erol!
This is really a useful function
Such a clear explanation! crystal clear.... â€
Glad you think so!
Thank you, Leila is the best teacher on the tube.
Wow, thank you Mike! đ
Very useful. Thanks again, Leila.
Your videos are great! Thank you for all you do. Is it possible to add data to the source list by typing into the combo-box? Thank you!
I just love your videos and tips nice work! And greetings from MĂ©xico
I'm glad to hear that! Greetings to MĂ©xico.
Hi Leila. I have just discovered this method and am greatly impressed. Is there a way of copying the formula to other rows? I tried but can't figure out how to change the reference cell to reflect the next row. Your advice would be appreciated. Thanks.
Very usefull, thank you!
Your channel is awesome!!! Thank you so much for sharing your skills.
Glad to have you here.
Thanks my dear for excellent solution
Absolutely awesome đ Leila you are great
This vedio is really educative. Thank you so much.
Thank You very much. God bless you.
Hi Leila, love your videos! My question is, how would this be applied to horizontal lists? I tried using the filter formula in the data validation but I am getting errors.
Great Video. Leila Just a question.
I have a Main worksheet with names linked with another worksheet with drop down list of same names.
Can I make this drop down list to read the same name clicked on the Main worksheet.? rather than showing names in order.
Thanks.
Thank you very much!!
WOW! Masterfully mindblowing !! Thanks a lot Leila :)
I'm glad you like it Evgenia! Hope it will be helpful for you.
wonderful work, i admire you really