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!
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 UA-cam. It gets me over that "what's wrong with me, why can't I get this" hump. Thank You.
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 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.
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.
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*
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.
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!
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!!!!!
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 👍🏼
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.
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
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 UA-cam... Keep on.. pls. create a new videos for the new version 2019 features for advanced excel...
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!
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
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!
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!
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 😊
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.
Thank you for your feedback. You found a smart solution by using one character per name. I'm glad the Choose video helped. How long are your lists? Is it possible to condense them to tabular lists instead - like I show in this video? ua-cam.com/video/avVLznHODVA/v-deo.html
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!
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.
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 :)
Wow! Your Infographics and dynamic arrays are eye opening! I was looking at UA-cam videos in this channel "Wawamustats". And you are the right person to ask this question.. Can I create such charts using Excel?
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....
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.
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
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
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.
Hello, Many thanks for helping us with your simple yet step by step process with respect to Excel. I tried the offset method and am running into issues. 1. I see # value error but when i press f9 I can still the full still. Q: Why does this error is shown ? 2. When I copy the formula and paste under data validation , it shows the dependant lists but the 2nd list doesn't change when the 1st Drop down values are changed ? Any help in this regard would be highly appreciated . Please note , I would want to avoid macros usage as much as possible due to limitations .
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!
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 UA-cam. It gets me over that "what's wrong with me, why can't I get this" hump. Thank You.
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 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.
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.
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!
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.
Thank you for the tip!
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.
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.
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 👍🏼
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.
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 cant thank you enough , have been trying for ages going thru several youtube videos , and you finally solved my problem......thank you....thank you
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.
I like your way of explaining which can be understood well by all audiences.👍
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.
Any excel lover who love excel will not resist subscribing your channel.
Cool stuff Leila. Thank you very much
Thanks so much for going over multiple methods! The Offset method worked best for my purposes, great tutorial 👍
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 UA-cam... Keep on.. pls. create a new videos for the new version 2019 features for advanced excel...
Solved; Leila made another video for more offset fn tutorials at the link: ua-cam.com/video/7mo4COng7Sg/v-deo.html; ***** five stars
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 :)
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 :)
Love this video! My go to Excel help. I prefer the table method since it's dynamic and I can sort each table independently.
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
I'm using Excel every day but when i see your lessons for Excel i love Excel too much. ♥️
Simply Incredible....No words for your excellence. I thought I was good in excel. Today i realized...i know nothing 🙂
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%
Putting a formula in the Name Manager for use in Data Validation... brilliant!
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!
Your Excel videos are the best! Very easy to understand! THANK YOU!
Glad to hear that!
Wow! I have attempted the offset function and it worked out well. Thank you!!!!
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!
Awesome, the second option I believe is the right one when more than one people is using the file. Really cool!
Agree - It's easier to understand and update :)
The named range trick to get around the data validation limitation is awesome.
Thanks. I hope Microsoft will improve on the data validation box.
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.
you do the best excel tutorials online, fact. Just wish you did a few more about macros though :(
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.
Thank you for your feedback. You found a smart solution by using one character per name. I'm glad the Choose video helped. How long are your lists? Is it possible to condense them to tabular lists instead - like I show in this video? ua-cam.com/video/avVLznHODVA/v-deo.html
You have solved my lot of problems leila, thanks a lot..
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!
You could try downloading my workbook (pinned comment) and compare with your solution.
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.
Thank you, Leila is the best teacher on the tube.
Wow, thank you Mike! 😃
I was actually looking 3rd part of the video (deal with table)for my solution, hats off to you again.
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!
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.
A huge thank you for your videos. Great tutorials that have really helped my own business.
Thank you for these videos.
I find your videos very interesting and helpful. thumbs up for you.
Glad you like them!
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 :)
Great to hear, Jan!
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.
Dear madam a another series of your amazing videos
Really it is eye opening
Your voice is so very "comforting" - great video
Thank you. I'm glad to hear that :)
More than GREAT, very useful formula. Many thanks
Mind blowing functioning by Leila. Learnt a lot from this clip. Weldone...
Thanks for the video - the second approach is much easier
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 :)
You're great Leila, pretty helpful videos; I love your explanation way
Thank you! 😃
By God's grace I got a teacher like you
That's very kind Sumit. I'm blessed with wonderful students :)
Wow! Your Infographics and dynamic arrays are eye opening! I was looking at UA-cam videos in this channel "Wawamustats". And you are the right person to ask this question.. Can I create such charts using Excel?
Great Leila, this training was super helpful
Thanks a lot Leila, for your wonderful contribution to future. Thanks again
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..
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....
You're welcome Bart. Yes - I know you're an INDIRECT fan :)
Your channel is fantastic! Heep up the great work and thanks for sharing all these GREAT tips and tricks.
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.
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
Yeah it worked, I didn’t know about the wildcard feature in excel. You the MVP
very super drop down list both formula offset and index match👍
This vedio is really educative. Thank you so much.
Exactly what I needed right now! Very helpful!
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
You're very welcome! I'm glad I could help with that.
Love it. Very informative and easy to follow.
Thanks, very useful video to create a dependent list.
Very cool. Have not seen the area method before. Great solution.
Thank you. Hadn't used this in a while so I thought it was a good opportunity to put it to use.
U are my teacher leila and u r an awesome lady . thumbs up 👍
Thank you! 😃
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!
Such a clear explanation! crystal clear.... ❤
Glad you think so!
I just love your videos and tips nice work! And greetings from México
I'm glad to hear that! Greetings to México.
Your channel is awesome!!! Thank you so much for sharing your skills.
Glad to have you 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.
Hey, Leila
Thanks for such wonderful technique , as usual you are awesome again...I like 1st method ..Keep sharing ..Very Nice
Your videos are amazing Leila, this helped me a lot!
The quality of your videos is amazing!!
I'm glad you like the videos Ismael!
Great video! Very useful and easy to follow. Thanks a ton!
Great video, well presented as always. Many thanks.
Exactly what I needed. Thank you 🙏
This is beautiful..... Exactly what I needed. Thanks Leila
You're very welcome. I'm glad you like it.
Hello, Many thanks for helping us with your simple yet step by step process with respect to Excel.
I tried the offset method and am running into issues.
1. I see # value error but when i press f9 I can still the full still. Q: Why does this error is shown ?
2. When I copy the formula and paste under data validation , it shows the dependant lists but the 2nd list doesn't change when the 1st Drop down values are changed ?
Any help in this regard would be highly appreciated .
Please note , I would want to avoid macros usage as much as possible due to limitations .
Thanks my dear for excellent solution
Wow - you taught me a bunch in 16 minutes! Thank YOU!!!
Great video visual style and content as well. Thank you Leila
Thank you Erol!
Well done, Ms Gharani! Very informative. Excel users will surely love your tutorials! Keep it up.
Thank You very much
Thanks for your support..
Wonderful video, could you please tell me how to create a dropdown list with new data entry in the same field.
You are fantastic Leila
Superb!!!!! very very useful. good explanation in cool way!
Thank you Jaipal!
Wow absolutely a great video thank u so much Ms. Liela
Thank you Ismail :)
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?
Thank You Leila, you have been very helpful!
this has been a massive help in my analysis - thanks Leila :)
You're very welcome Phil!
awesome Leila! amazing work!! thank you
Thank you! Cheers!
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.