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

[@LeilaGharani]

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

[@carlladd119]

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

[@carlladd119]

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.

[@anillukhi9335]

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

[@mazahiranwar2143]

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

[@andrehinds7501]

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!

[@mdcs1992]

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

[@avbestlife71]

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

[@einyv]

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.

[@MrSupernova111]

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.

[@SA-vh3if]

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

[@LeilaGharani]

Glad to hear that!

[@conservativemike3768]

Thank you, Leila is the best teacher on the tube.

[@LeilaGharani]

Wow, thank you Mike! 😃

[@wattjock]

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

[@AmitChandra_YouTube]

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.

[@Kooseandco]

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

[@atps]

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*

[@LeilaGharani]

Thank you for the thumbs up!

[@grantw1465]

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

[@LeilaGharani]

Thank you Grant - that's really kind! I'm really glad you find the tutorials helpful for your work.

[@OffRoadVee]

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!

[@LeilaGharani]

Thank you for the kind feedback.

[@ve4lasses242]

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

[@opt6037]

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

[@scottwillis9474]

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.

[@pac0113]

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

[@nelsonachoka7918]

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

[@kuuuyajim]

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

[@LeilaGharani]

I'm glad to hear it worked out Jim.

[@zarimajumakanova9223]

More than GREAT, very useful formula. Many thanks

[@tbg2222]

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, [e-mail].

[@saileshmutreja9311]

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.

[@LeilaGharani]

You're very welcome. Glad you found something new here :)

[@hardockalaweya1783]

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

[@LeilaGharani]

Glad you like them!

[@oilwings]

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

[@luisneto2165]

Very useful. Thanks again, Leila.

[@Kiyoshi_9606]

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.

[@LeilaGharani]

Thank you for the tip!

[@aashaygamer]

You are fantastic Leila

[@TheYollin]

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

[@HollandHillSpies]

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

[@utibe007]

Totally agree... In fact, I just used the table method right now

[@germanpetslover.lahori.8184]

@@utibe007 1%qqqqq%qqqqqq%

[@ExcelForFreelancers]

Great Leila, this training was super helpful

[@saddamhossain9041]

This vedio is really educative. Thank you so much.

[@ManuelVargas619]

Love it. Very informative and easy to follow.

[@goyalmukeshkumar]

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

[@tanjirulhaque1585]

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

[@LeilaGharani]

Thank you for the kind words. I'm glad you like the videos.

[@Galileo2pi]

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

[@LeilaGharani]

Thank you! 😃

[@professoruma]

Exactly what I needed. Thank you 🙏

[@sumitthakur2241]

By God's grace I got a teacher like you

[@LeilaGharani]

That's very kind Sumit. I'm blessed with wonderful students :)

[@laicasalise6224]

Exactly what I needed right now! Very helpful!

[@amralsabban3533]

Thanks for the video - the second approach is much easier

[@karlbekker777]

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

[@GhulamAli-ge8gi]

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

[@LeilaGharani]

Thank you! 😃

[@AhmadMohassebElroby]

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

[@eng.magesakhamis493]

You are the best Leila👍👍

[@bensharratt8451]

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

[@54788963]

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!

[@LeilaGharani]

Many thanks for your support!

[@psyfafa77]

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

[@LeilaGharani]

Agree - It's easier to understand and update :)

[@FamilyCare-km3zn]

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 KZbin... Keep on.. pls. create a new videos for the new version 2019 features for advanced excel...

[@FamilyCare-km3zn]

Solved; Leila made another video for more offset fn tutorials at the link: kzbin.info/www/bejne/bZ7SZXaFo8xqick; ***** five stars

[@akashology]

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

[@mallikarjunb6143]

Thanks, very useful video to create a dependent list.

[@sonamtseringsherpa]

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

[@LeilaGharani]

Glad you think so!

[@gabosdad4007]

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

[@vrishalchawathe1516]

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

[@rsn29sep]

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

[@Things-Recycleing]

Thanks my dear for excellent solution

[@crazycatl80]

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!

[@LeilaGharani]

You're very welcome, Wendy!

[@nikhilgohel2069]

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

[@rajauddin5315]

Thanks for your support..

[@alvaro_gavilan_rojas]

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!

[@LeilaGharani]

Great to have you here Alvaro :)

[@pedrojorgetavaresrdealmeid9896]

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!

[@LeilaGharani]

You could try downloading my workbook (pinned comment) and compare with your solution.

[@maykazama]

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

[@LeilaGharani]

Many thanks for the feedback! New video coming each week :)

[@TimSheets]

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

[@LeilaGharani]

Thanks. I hope Microsoft will improve on the data validation box.

[@nipunkavinda8254]

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

[@janhoffken7850]

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

[@LeilaGharani]

Great to hear, Jan!

[@Karen-bm3rj]

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

[@LeilaGharani]

I'm glad to hear that Karen. It's great you can apply it right away.

[@philipmarno4989]

Great video, well presented as always. Many thanks.

[@ismailismaili0071]

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

[@LeilaGharani]

Thank you Ismail :)

[@khalidQweder]

Great Teacher.

[@MrLilipili]

awesome Leila! amazing work!! thank you

[@LeilaGharani]

Thank you! Cheers!

[@ramontan7620]

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!

[@LeilaGharani]

Many thanks for the kind feedback!

[@iziaurrahman]

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

[@TheSmartodd]

Your voice is so very "comforting" - great video

[@LeilaGharani]

Thank you. I'm glad to hear that :)

[@doorbs]

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 😊

[@LeilaGharani]

I'm glad this helped! Thanks for the feedback.

[@AbhishekTripathidce]

Thank you very much , wat a great tutorial it is ...

[@pinakikkkm]

Great solution...thank you

[@GeraSanz]

I just love your videos and tips nice work! And greetings from México

[@LeilaGharani]

I'm glad to hear that! Greetings to México.

[@81siddharth]

Absolutely awesome 👍 Leila you are great

[@harishpaldhir5915]

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

[@Howdy1836]

After watch your first index drop-down list, I was so much hoping you had a follow-up. You are my new sensei. Great video, Thank you. =OFFSET('Table_ER_GL_Acoounts'!$A$1,1,MATCH(INDEX(ER_Worksheet!$C$2:$C$30,A24,0),'Table_ER_GL_Acoounts'!$A$1:$F$1,0)-1,COUNTA(OFFSET('Table_ER_GL_Acoounts'!$A$1,1,MATCH(INDEX(ER_Worksheet!$C$2:$C$30,A24,0),'Table_ER_GL_Acoounts'!$A$1:$F$1,0)-1,100,1)),1)... geez...lol

[@durandtvermaak4219]

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

[@LeilaGharani]

Glad to have you here.

[@evgeniam8882]

WOW! Masterfully mindblowing !! Thanks a lot Leila :)

[@LeilaGharani]

I'm glad you like it Evgenia! Hope it will be helpful for you.

[@krn14242]

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

[@LeilaGharani]

Thank you. Hadn't used this in a while so I thought it was a good opportunity to put it to use.

[@jaipalrana.]

Superb!!!!! very very useful. good explanation in cool way!

[@LeilaGharani]

Thank you Jaipal!

[@NanaAmpemDarko]

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

[@LeilaGharani]

You're very welcome. I'm glad you like it.

[@dsquare7723]

Thank You Leila, you have been very helpful!

[@petardebeljakovic8265]

Absolutely agree with all the comments below

[@IRG0203]

The quality of your videos is amazing!!

[@LeilaGharani]

I'm glad you like the videos Ismael!

[@ericbrusky880]

Thank you so much this is amazing!

[@MrErolyucel]

Great video visual style and content as well. Thank you Leila

[@LeilaGharani]

Thank you Erol!

[@binodthakur315]

So helpful to me.. Thank You Mam.

[@LeilaGharani]

I'm glad to hear that.

[@kethanchordia]

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

[@rensycloren9338]

Great explanation.....!!!

[@LeilaGharani]

Glad you like it!

[@Andrius1115]

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.

[@antnfs]

I know you posted this a long time ago and got no responses but want you to know that if this works it’ll give a solution to the exact problem I have so thank you

[@antnfs]

Yeah it worked, I didn’t know about the wildcard feature in excel. You the MVP

[@ges05]

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.

[@cleanpoop9929]

Whoa. That was great. Thanks!

[@MarceloFerreira-mw5cv]

Your videos are amazing Leila, this helped me a lot!

[@chrismcnair6646]

Sweet! I figured it out. Thanks for the help!

[@Monika-hx9ke]

Very usefull, thank you!