Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells 

Grab the file I used in the video from here 👉 pages.xelplus.com/dynamic-dropdown-file

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!

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.

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 RU-vid. It gets me over that "what's wrong with me, why can't I get this" hump. Thank You.

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.

Any excel lover who love excel will not resist subscribing your channel. Cool stuff Leila. Thank you very much

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 👍🏼

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

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.

I like your way of explaining which can be understood well by all audiences.👍

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

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, Leila is the best teacher on the tube.

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*

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.

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.

Your Excel videos are the best! Very easy to understand! THANK YOU!

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!!!!!

Thanks Leila! two thumbs up for both methods, I find the table method far more intuitive and easier to recall and reuse.

Putting a formula in the Name Manager for use in Data Validation... brilliant!

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! ❤️

Thanks so much for going over multiple methods! The Offset method worked best for my purposes, great tutorial 👍

Leila, I just discovered your tube stuff know... Congrats!! Your vision about how to drive through the Excel issues is so bright! Even so, I'm dealing with something I can't find the solution » After using Offset formula, with the CountA and Match as you did, my final result is not one match data but a list of all table I've on my data attached. I reviewed several times the video, already tried to exactly copy the same formula, but the result is always the same. Did you have someone facing similar issue? Thanks a lot for your feedback!

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.

I'm using Excel every day but when i see your lessons for Excel i love Excel too much. ♥️

Perfect Lelia; smooth teaching; but offset is hard to understand as described quickly; so table method is the best ; can you pls. make a special video for Match & offset parameters... You are one of the best Excel teachers on RU-vid... Keep on.. pls. create a new videos for the new version 2019 features for advanced excel...

you do the best excel tutorials online, fact. Just wish you did a few more about macros though :(

Wow! I have attempted the offset function and it worked out well. Thank you!!!!

Love this video! My go to Excel help. I prefer the table method since it's dynamic and I can sort each table independently.

You have solved my lot of problems leila, thanks a lot..

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!

Thanks a lot Leila, for your wonderful contribution to future. Thanks again

More than GREAT, very useful formula. Many thanks

Awesome, the second option I believe is the right one when more than one people is using the file. Really cool!

THANK YOU VERY MUCH! I was trying to do it 2 days already !!! I have tried with CELL NAMES from another sheet and INDIRECT function for DATA VALIDATION, but it seems that it's not possible that way... For those who have formulas (with " " results) instead of plain data, I suggest using COUNTIF formula with a criteria of "?*" ---> that way you won't get blank values in a dropdown menu.

This vedio is really educative. Thank you so much.

Thanks for the video - the second approach is much easier

You are fantastic Leila

Thank you for these videos. I find your videos very interesting and helpful. thumbs up for you.

Simply Incredible....No words for your excellence. I thought I was good in excel. Today i realized...i know nothing 🙂

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

Love it. Very informative and easy to follow.

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!

Great Leila, this training was super helpful

Very useful. Thanks again, Leila.

By God's grace I got a teacher like you

Leila, you are great!! And another great video of yours. Thank you very much! Nevertheless, I'm having a problem with the data validation. For some reason, I just get as error when clicking "ok" after pasting the formula, in the "data validation" dialog box... The formula it's the same as yours. The only difference is that I have the main data in another sheet. Could that be a problem? I believe not. Many thanks and all the best.

All of your videos are outstanding. Please keep up your great work. Thanx again.

Your voice is so very "comforting" - great video

A huge thank you for your videos. Great tutorials that have really helped my own business.

U are my teacher leila and u r an awesome lady . thumbs up 👍

Exactly what I needed right now! Very helpful!

Thanks my dear for excellent solution

You are the best Leila👍👍

Hot Tip: If they don't need to be dynamic, just select a *huge box* of all your data, then *F5 to select Constants, then Ctrl+Shift+F3 and now you won't have any "blank" options*. Simpler, but not dynamic. Much simpler if you don't need dynamic lists; but if you do need dynamic lists, this video is a life saver.

Such a clear explanation! crystal clear.... ❤

Exactly what I needed. Thank you 🙏

You're great Leila, pretty helpful videos; I love your explanation way

I was actually looking 3rd part of the video (deal with table)for my solution, hats off to you again.

Thanks a lot! It is so useful, in this video list and selecting options are kept in one sheet, if we want in different sheet to avoid all information available on same sheet, how can we do?

Hi Leila. I have developed a drop down list using a sequence function and then linked it to data using a vlookup from a table in another sheet. however, when i reduce the list by selecting a smaller number, the number of rows beyond the selected column number do not get eliminated. what formula can be used to eliminate this and make these rows more dependable on the first column. Thank you for always being so helpful and providing such great content.

very super drop down list both formula offset and index match👍

Thanks for your support..

Mind blowing functioning by Leila. Learnt a lot from this clip. Weldone...

Thank you Leila. I did never consider the 2nd way of the Index function. I will give it a try. If you have lots (>5?) of columns I would consider your solution. Otherwise I would prefer the table solution with names and INDIRECT....

This is an elegant solution and awesome. Thank you for this video! There is, an issue, however. I understand I am the odd case, BUT, this solution is still difficult for those who have many lists. I have a situation where I have over 80 different lists that could appear based on a selection in another drop down. The formula bar (or "Refers To" bar) in the Name Manager has a character limit (albeit longer than the formula bar in the Data Validation > List formula bar). As you can imagine, many tables can generate a lot of characters in the formula. The other issue is, if you need to drag the formula down to other cells, it always is referencing the same named range and therefore not flexible to apply in many locations (unless you create other named ranges). Your video on using the CHOOSE function has been the most flexible and amazing solution I've found thus far. The best workaround I have found for cases like mine is that I assign a list a name and use the characters from Windows' Character Map. This way, each list is one character long and allows me as many tables as I can get. If you want to see my example, let me know because it always helps with a visual representation. As always, thank you so much for your videos Leila. Massively helpful.

I have just written below your video about the dependent combo boxes.. This is the start of solving my described problem... But I'm getting a new one right away. ;) Is there a way to implement this tutorial into the combo boxes. They are much nicer for my sheet... I'll test arround. Perhaps I'm getting a solution or find another of your videos. Thx btw. For the help. It's easy to understand and simple to follow. Have a nice and sunny day

Thanks, very useful video to create a dependent list.

The named range trick to get around the data validation limitation is awesome.

Your channel is fantastic! Heep up the great work and thanks for sharing all these GREAT tips and tricks.

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!

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.

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 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! :)

Very helpful, many thanks. When I change "Productivty" to "Game" in my own file the cell value doesn't change automatically, though the dropdown values are correct, please how do I fix this?

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!

The second method is genius and far superior compared to the first method (volatile functions)! Amazing channel! Even Excel-Pros can get some very handy tricks here :)

I subscribe as I love all your tips. Your dependent drop down list videos were very helpful. Unfortunately, the solution doesn’t seem to work with Excel’s data validation in tables. Did I miss something? Regardless, I will continue to benefit from your knowledge and excellent teaching skills.

Dear madam a another series of your amazing videos Really it is eye opening

Amazing tip..!!! People don't care to like your video even though they use it.. 600 is quite low for this video :-) I love all your tips..

Hi Leila, I love this trick you are awesome. I have a doubt I applied the OFFSET and it is working but only thing is when I change the source cell the destination cell default selection remains the old dropdown value. How can i refresh and show the values connected to the source selection as default thanks in advance?

Very cool. Have not seen the area method before. Great solution.

Great Teacher.

Hi Leila, Yet another great video, clear and easy to follow. Thanks. I'm testing the functionality now on one of my workbooks.

This is beautiful..... Exactly what I needed. Thanks Leila

Great solution...thank you

So helpful to me.. Thank You Mam.

It's difficult to complement you, as I was struggling for this for last couple of months, used table but it wouldn't appear in datavalidation, and what a simple trick u just explained in this video.. kuddos to you and your team

Wow absolutely a great video thank u so much Ms. Liela

Wonderful video, could you please tell me how to create a dropdown list with new data entry in the same field.

Leila, great video thanks. I am using activeX combo list boxes with the data validation, but using this index offset formula in the data validation is not allowing the dependent combo box to work - it opens to a blank line. The parent combo works fine. Your help is appreciated.

Thank you for your video, I'd like to ask what if we change values in dropdown list tables and I want these new values automatically to be updated in the big table (in which I selected the old values), how can we do that? Thank you.

Great video! Very useful and easy to follow. Thanks a ton!

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!

Great video, well presented as always. Many thanks.

awesome Leila! amazing work!! thank you

Wow! Your Infographics and dynamic arrays are eye opening! I was looking at RU-vid videos in this channel "Wawamustats". And you are the right person to ask this question.. Can I create such charts using Excel?

Your channel is awesome!!! Thank you so much for sharing your skills.

Thank You Leila, you have been very helpful!