Of the 20+ videos I have watched describing how to set up dependent drop down lists, yours was the easiest to follow and to implement. Its dynamic update capability enables content managers with zero knowledge of Excel functions to maintain their drop down lists. Thank you for this elegant solution.
@@TEKNISHA, I'm still working on this. I've discovered a variation on your approach that works even better. Instead of grouping multiple look up lists into a single table with many columns, one column per list, I'm going to create a table for each drop down list consisting of a single identically named column, e.g. Value. This means that my data validation string will concatenate the table name, not the column. Because each table has one column, it becomes easy to delete rows containing spaces and to sort the list of rows without disrupting other drop down lists.
Thanks you for this tutorial. This is the best way for multiple dependent dropdown list as long as you can accept the empty entries in a dropdown list (if you have empty cells in a table column). Using the Offset function to avoid this is much more complicated. You just got a new Subscriber.
Dear Bharah, Many thanks for such an informative and useful video, this approach is quite easy and my long search got ends with your videos. I was desperately searching for exactly this kind of dynamic dependent drop-down list with a formatted table
First one to explain how to create multi level dependent drop down lists without complicated formulas - Just two steps - Format Data as Table and single Indirect function to call dropdown lists! Thanks a lot.
great!! thank you for the tutorial.. absolutely cool !! can i go further? for instance, to create dropdown list for province and then districts and so on? update: yes i can expand it up to as many levels as i want.. thank you again
@@TEKNISHA Bro Thanks so much. One Question Though: After we select "Africa" in continent" and "Nigeria" in Country, in the city column under drop down it also shows BLANKS as a Valid Option, How shall we remove those BLANKS from being available as a valid option coz they are invalid.
Hello Teknisha, huge fan. I would like to know how you solved the "This needs to be delimited error"? It only occurs on the browser version of Excel. Everything works fine in the desktop version... But on the browser version it returns that error
Glad to be of help. Unfortunately in this method blank cells cannot be removed. If offset formula method is used to define a range , the blank cells can be avoided. However it is good only for smaller number of dependent options..
Thanks so much for this. Is there a way to get rid of blank cells appearing in the drop down e.g. columns Nigeria, Kenya, South Africa don't have as many values as the others and as such creates a blank cell on drop down options. I have columns with 10 entries in one and 50 in the next which means scrolling through to find the selection in drop down. Thanks
Glad that this helped you. This method is easy to implement, but it will have the blank row issue if there are different number of options. If you want dynamic, it can be achieved using offset formula, but that approach will become a little cumbersome to implement if you have many level of dependencies.
HI, thanks very much for the video. It is crystal clear. But, I am unable to refer it on another worksheet and it's giving me an error. What to do in this case ? My actial table is on another sheet whereas the lists are on another sheet. Please reply.
Dear Bharah, Many thanks for such an informative and useful video, this approach is quite easy and my long search got ends with your videos. I was desperately searching for exactly this kind of dynamic dependent drop-down list with formatted table...need your help as while executing dependent dropdown with cell reference i am getting an Error message " The List Source must be a delimited list or a reference to a single row or column. just sent my file to🙏🙏 your email ...many thanks in advance for your help..
@@TEKNISHA Mant thanks dear for your lightning fast reply !... please advise if can use the "Unique" function to hide blanks and "Sort" function psooibility
When I get select the formula =indirect("XXX[XXX]") this formula is not working, which excel version needs to use , I have Mulitple station and Defect w.rt. station so not able to make the formula, what was worng in the excel
Bro Thanks so much. One Question Though: After we select "Africa" in continent" and "Nigeria" in Country, in the city column under drop down it also shows BLANKS as a Valid Option, How shall we remove those BLANKS from being available as a valid option coz they are invalid.
Glad that you liked the video. However with this method (tables) it is not going to be possible to remove the blanks as of now. You can look at using named ranges and offset method but that will involve more formulas as you add options.
It depends on how many options you have in each level. You can check this video of mine. This method can be used too if your number of options are not going to change. Excel Create Dependent Drop Down List Tutorial ua-cam.com/video/TkZV7KzrFc8/v-deo.html
Hi, i want to ask. Can we auto populate which country is the city from? For example, if i just type the name of city, the it automatically fill which country it is from
Hi, are you doing this in desktop Excel or 365? I'm trying it in the web version and when entering source information in the data validation box for my second (dependent) column, I get the error message "list source must be a delimited list, or a reference to single row or column". Any feedback on ways I might address this issue would be wonderful. Thank you.
@@TEKNISHA Thanks for your early feedback. I'm trying but maybe that link is not working for me 😦. Is it possible to give me that file by email, please?
I'm already trying many of times to give the email address here. Do you have any idea? or is it possible to give another download link here. Please don't mind, actually I'm not found another way.
Hello.I badly needed your help.I just created similar project by following your instruction step by step.Unfortunately I cant finish because I am lost.I am doing 5 expandable.Please help me to finish my file.Really appreciate
@@TEKNISHA oh my God.Thank you so much.I cant believe you replied back.Thank you so much.My presentation will be on Monday.I am right now infront of my laptop having trouble.Just sent you the file.
Sir i am having two doubt 1. When at last only one option is there than why not coming automaticaly 2. when we are selecting some other name in any dropdown then why previous data is still there
BE CARFUL! If you use a formula with a & between the row & column, It's going to get data from the first value! ❌=INDIRECT("Countries[$B$5]") ✔=INDIRECT("Countries[$B5]") or works with =INDIRECT("Countries[B5]")
Of the 20+ videos I have watched describing how to set up dependent drop down lists, yours was the easiest to follow and to implement. Its dynamic update capability enables content managers with zero knowledge of Excel functions to maintain their drop down lists. Thank you for this elegant solution.
Thank you Gary. Glad that this solution helped you.
@@TEKNISHA, I'm still working on this. I've discovered a variation on your approach that works even better. Instead of grouping multiple look up lists into a single table with many columns, one column per list, I'm going to create a table for each drop down list consisting of a single identically named column, e.g. Value. This means that my data validation string will concatenate the table name, not the column. Because each table has one column, it becomes easy to delete rows containing spaces and to sort the list of rows without disrupting other drop down lists.
Great Gary. 👍🏼
Great work mate - best solution I’ve seen
Thanks you for this tutorial.
This is the best way for multiple dependent dropdown list as long as you can accept the empty entries in a dropdown list (if you have empty cells in a table column).
Using the Offset function to avoid this is much more complicated.
You just got a new Subscriber.
Thank you for subscribing. Happy that this tutorial helped you.
HAVE BEEN WATCHING FOR SO MANY TUTORIALS AND FINALLY THIS WORKS😭😭OMG THANKSSS BRO
Glad I could help
WOW, this is fantastic, THANK YOU. I could not get around words with spaces in them previously, this completely solved the problem.
You're very welcome!
Easy explanation and it works in Excel 2019. Well done 👍
Glad it Helped!!
Looked at a lot of videos. This one is the best solution out there. Thank you, it was very helpful.
You can't imagine how grateful I am for this video!
You're amazing! :)
This is so nice of youu.. kudosss shud have more likes, its very simple and helpful
Glad that this helped you.
Dear Bharah, Many thanks for such an informative and useful video, this approach is quite easy and my long search got ends with your videos. I was desperately searching for exactly this kind of dynamic dependent drop-down list with a formatted table
Can't agree more. simple and concise. Congrats and thank you!
Thank you my friend for this tutorial.
Very helpful to my work
Glad that this helped you
First one to explain how to create multi level dependent drop down lists without complicated formulas - Just two steps - Format Data as Table and single Indirect function to call dropdown lists! Thanks a lot.
Glad that this tutorial was of use to you
Thank you so much. It has helped me a lot
Excelent this video, I'm really greatfull, that help me to improve the report in my job. Thank you so much....!!!!
great video
thank you so much!
Glad that this helped.
Nice
Thanks
New subscriber here. Your video is very useful. Thank you so much!
Thank you for subscribing… glad that the video helped you..
God bless you my dear
great!! thank you for the tutorial.. absolutely cool !!
can i go further? for instance, to create dropdown list for province and then districts and so on?
update: yes i can expand it up to as many levels as i want..
thank you again
You are welcome. You are right. With this method you can easily go any number of levels.
@@TEKNISHA Bro Thanks so much. One Question Though: After we select "Africa" in continent" and "Nigeria" in Country, in the city column under drop down it also shows BLANKS as a Valid Option, How shall we remove those BLANKS from being available as a valid option coz they are invalid.
thanks #excel tut
ITS WORK. Thanks for this tutorial. already subscribe n like. :-)
Happy that this helped
Hello Teknisha, huge fan.
I would like to know how you solved the "This needs to be delimited error"?
It only occurs on the browser version of Excel. Everything works fine in the desktop version... But on the browser version it returns that error
Thank you for this! How can we remove the blank cells from the drop down lists?
Glad to be of help. Unfortunately in this method blank cells cannot be removed.
If offset formula method is used to define a range , the blank cells can be avoided. However it is good only for smaller number of dependent options..
Thanks so much for this. Is there a way to get rid of blank cells appearing in the drop down e.g. columns Nigeria, Kenya, South Africa don't have as many values as the others and as such creates a blank cell on drop down options. I have columns with 10 entries in one and 50 in the next which means scrolling through to find the selection in drop down. Thanks
Glad that this helped you. This method is easy to implement, but it will have the blank row issue if there are different number of options. If you want dynamic, it can be achieved using offset formula, but that approach will become a little cumbersome to implement if you have many level of dependencies.
@@TEKNISHA I figured it out and got the drop downs I need with no blank rows! Thanks for the video and advice :)
Nice.. how did you achieve it
@@lucym1741 how did you remove the blanks?
please explain how to sort (A to Z) a list within the table
HI, thanks very much for the video. It is crystal clear. But, I am unable to refer it on another worksheet and it's giving me an error. What to do in this case ? My actial table is on another sheet whereas the lists are on another sheet. Please reply.
It should work on another worksheet, as you are referring the table name. What is the error you are getting?
Hi There, I am trying to use the formula in 4:23 and cannot get this to work. I am trying to do a drop down for 3 levels, but its a bit complicated
Dear Bharah, Many thanks for such an informative and useful video, this approach is quite easy and my long search got ends with your videos. I was desperately searching for exactly this kind of dynamic dependent drop-down list with formatted table...need your help as while executing dependent dropdown with cell reference i am getting an Error message " The List Source must be a delimited list or a reference to a single row or column. just sent my file to🙏🙏 your email ...many thanks in advance for your help..
Glad that this solution will help you. I got your email and have made the changes and resent. Hope it works for you.
@@TEKNISHA Mant thanks dear for your lightning fast reply !... please advise if can use the "Unique" function to hide blanks and "Sort" function psooibility
When I get select the formula =indirect("XXX[XXX]") this formula is not working, which excel version needs to use , I have Mulitple station and Defect w.rt. station so not able to make the formula, what was worng in the excel
Bro Thanks so much. One Question Though: After we select "Africa" in continent" and "Nigeria" in Country, in the city column under drop down it also shows BLANKS as a Valid Option, How shall we remove those BLANKS from being available as a valid option coz they are invalid.
Glad that you liked the video. However with this method (tables) it is not going to be possible to remove the blanks as of now. You can look at using named ranges and offset method but that will involve more formulas as you add options.
@@TEKNISHA so if my need is only upto 3 levels, Offset is best?
It depends on how many options you have in each level. You can check this video of mine. This method can be used too if your number of options are not going to change.
Excel Create Dependent Drop Down List Tutorial
ua-cam.com/video/TkZV7KzrFc8/v-deo.html
Let me try this with MS Office Excel 2013 if this will work.
I hope it worked for you
@@TEKNISHA mine is a database with duplicated words.
@@emzeguna602 me too. did u get the solution? pls share
Hi, i want to ask. Can we auto populate which country is the city from? For example, if i just type the name of city, the it automatically fill which country it is from
Yes, it is possible using any type of lookup functions like index/match, xlookup & vlookup.
Hi, are you doing this in desktop Excel or 365? I'm trying it in the web version and when entering source information in the data validation box for my second (dependent) column, I get the error message "list source must be a delimited list, or a reference to single row or column". Any feedback on ways I might address this issue would be wonderful. Thank you.
im having the same issue on mac
Hi, Can You Please give me a link to download this excel file? Your existing link is not working.
Hello Atiqur, The link is working. Kindly recheck
@@TEKNISHA Thanks for your early feedback. I'm trying but maybe that link is not working for me 😦. Is it possible to give me that file by email, please?
Hello Atiqur. Please give me your email id
@@TEKNISHA Brother if I input my email address here, my comments automatically removed 😦. Why I don't know. Maybe UA-cam don't allow this.
I'm already trying many of times to give the email address here. Do you have any idea? or is it possible to give another download link here. Please don't mind, actually I'm not found another way.
Hello.I badly needed your help.I just created similar project by following your instruction step by step.Unfortunately I cant finish because I am lost.I am doing 5 expandable.Please help me to finish my file.Really appreciate
Hello jonas,
Sure. Send me your file & details to bharath@teknisha.com. I will see how I can help
@@TEKNISHA oh my God.Thank you so much.I cant believe you replied back.Thank you so much.My presentation will be on Monday.I am right now infront of my laptop having trouble.Just sent you the file.
Hello jonas, I have made the changes and sent you the file. I hope this helps you.
@@TEKNISHA Hello!Yes I received the file.And Yes this is the way I need it.Big thank you.You are so kind.
Glad that it is working
Sir i am having two doubt 1. When at last only one option is there than why not coming automaticaly 2. when we are selecting some other name in any dropdown then why previous data is still there
I can't also do this in Microsoft Excel 365 Online :(
BE CARFUL! If you use a formula with a & between the row & column, It's going to get data from the first value!
❌=INDIRECT("Countries[$B$5]")
✔=INDIRECT("Countries[$B5]") or works with =INDIRECT("Countries[B5]")
(this is just a reminder for people's watching)
👍👍👍👍👍