Dynamic Dependent Dropdown Lists in Excel: create and manage all the categories you need - T0032

[@CeliaAlvesSolveExcel]

Since this video was published, this technique was simplified. Watch this session to learn a much easier way to implement up to 3 dependent dropdown lists. kzbin.info/www/bejne/b4rdqYCrns2Sbacsi=3EUWBU9l8wdAscIj

[@zacharymbradleyjr]

I am 4 minutes and 24 seconds into your video and I had to pause to send you a HUGE thank you! I have watched so many videos regarding this subject and yours is the only one that is the most benificial. Granted, there are various ways to set up dependent drop down options, but yours is so easy and straight forward and the flexibility is amazing!!!!!

[@CeliaAlvesSolveExcel]

There you go! Glad to help, Zacharym! Enjoy! :)

[@KalaniR-dt6lt]

This is very useful and it worked. The way you teach step by step makes the all the difference compared to other videos. Thank you so much!

[@CeliaAlvesSolveExcel]

I am glad that you made it work for you. Well done!

[@AccessAnalytic]

Nice hack with the # at the end of the forrnula to pick up the array!

[@CeliaAlvesSolveExcel]

Thanks, Wyn! I was kinda surprised that worked in a name. 😁

[@zahoorsarbandi2982]

A very informative lecture on dependent dropdown list with a simple and easily understandable method of teaching. Thanks for the efforts.

[@CeliaAlvesSolveExcel]

Your very welcome, Zahoor. :)

[@IbrahimNajjar-by2dk]

Absolutely amazing! just solved an issue I have been struggling with for months. Thanks for the in depth explanation Celia!!!

[@CeliaAlvesSolveExcel]

Your very welcome, Ibrahim! Glad that it was helpful to you.

[@EricHartwigExcelConsulting]

Magic! This is really awesome! Thank you for creating and posting this video!

[@CeliaAlvesSolveExcel]

Awesome to see you are a Champion too! Thank you for watching and your feedback. 💪🏆🔥

[@Fabi_terra]

This tutorial is incredible! Thank you so much for taking the time to make it. I really appreciate your effort. 🤗🤗

[@CeliaAlvesSolveExcel]

You're welcome! Make sure to check the comments. There's a simplified version published by Wyn Hopkins that is easier to implement.

[@Fabi_terra]

@@CeliaAlvesSolveExcel Thank you! Is it possible to add a function to prevent someone from entering the same data twice on the source list?

[@CeliaAlvesSolveExcel]

@@Fabi_terra add a column to the table with countif. Range is the column where people enter the data. Criteria is the cell in that same column. Then put that inside IF and say is countif result is greater than 1 than "duplicate" else "".

[@Fabi_terra]

@@CeliaAlvesSolveExcel Hey Celia, thank you so much! I appreciate it! Have a lovely day.😘

[@CeliaAlvesSolveExcel]

@@Fabi_terra you're welcome! A lovely day to you as well.

[@rodyeltabaa6871]

Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then list the selected table's headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction.... happy to share my findings with you 😊

[@CeliaAlvesSolveExcel]

That's awesome! The INDIRECT function is very useful and unavoidable in many scenarios. When possible, I prefer not to use it because it is a volatile function , meaning that it is constantly recalculating with every change in the workbook. The technique you describe is what I used before the dynamic array functions. Another disadvantage of it is that the item names cannot have space character because neither can table names. There's ways to work around that, but extra work. Take a look at the video by Wyn Hopkins in his channel where he simplified the technique I presented in this video.

[@ziggle314]

Magic. Nice job, as always. Thanks for all your efforts.

[@CeliaAlvesSolveExcel]

Your very welcome! Thank you for watching till the end. 🔥🏆

[@amandeepchandhok7166]

I really want to thank you for this. i have been looking to come up with a solution and yours is the best.

[@CeliaAlvesSolveExcel]

You're very welcome, Amandeep. Enjoy! 😊

[@DavidWhittley]

Excellent. Thanks Celia. I managed to follow all of that and get everything working apart from the conditional formatting, which just did not work for me.

[@CeliaAlvesSolveExcel]

@@DavidWhittley well done! Check the comments. There's a simplified way without having to create all those names.

[@wayneroberts7429]

Magic! Thanks so much for this solution Celia.

[@CeliaAlvesSolveExcel]

You're welcome, Wayne! Glad that it helped. In the meantime, I've evolved to using a simpler technique that does not involve creating names. If interested, please see here kzbin.info/www/bejne/b4rdqYCrns2Sbacsi=xBMadi7-K7yBZIuA In this presentation I explain dropdowns from creating one up to creating 3 dependent ones.

[@AjayDhole]

Superb! I have wanted exact solution for last couple of days, really this solution made my day... :)

[@CeliaAlvesSolveExcel]

That's awesome! So glad that it helped you, Ajay.

[@Nelson10409]

Hello Celia, I inserted my dropdown lists in between other columns and noticed that if I apply filters; I lose the content of my dropdown lists...is there a way to apply filters wo losing them? Thanks

[@CeliaAlvesSolveExcel]

Hi, Nelson. I tested on my end and I do not get that behavior. I wonder if it had to do with filters at all. I recommend checking the formulas associated to the data validation. The $ need to be in the correct place. Give it a review and good luck. Thank you

[@Nelson10409]

@@CeliaAlvesSolveExcel , Thank you for reviewing this. 1. Found an error on the first data validation...I did not roll the formula to all table - this one is solved 2. Since I inserted the data validation formulas between other columns, when I apply filters; I only get them in one side of the columns...example have other data in columns "a & b" then data validation formulas in "c & d" and other data again in "e & f"; can I get them in both sides? Thanks again for your help.

[@CeliaAlvesSolveExcel]

@@Nelson10409 you control where the data validation applies. It all has to do with how you set the rules. Since I published this video, a sonogram technique came along. I suggest that you check the comments to find that other way which is much easier to implement.

[@CeliaAlvesSolveExcel]

Check this one: kzbin.info/www/bejne/b4rdqYCrns2Sbacsi=3EUWBU9l8wdAscIj

[@iankr]

Many thanks, Celia - this is ingenious and very well explained. PS: Your new hairstyle suits you!

[@CeliaAlvesSolveExcel]

Thank you, Ian. :)

[@DroneEyes]

Oi celia o link para baixar o XLS mencionado acima nao esta funcionando :( Voce poderia concertar e nos avisar para que possamos baixar o arquivo? Muito obrigado!

[@CeliaAlvesSolveExcel]

Olá, Vicente. Pode por favor verificar se agora já funciona? Depois de introduzir nome e email e confirmar, deve ter acesso a uma página onde está o link para um zip no one drive contendo 2 arquivos.

[@DroneEyes]

@@CeliaAlvesSolveExcel eu consegui baixar os arquivos, porem a coluna "H" nao esta mostrando os valores no dropdown, voce tem que digitar o nome para que o campo seja preenchido. nao sei se foi essa a sua intencao mas muito obrigado

[@CeliaAlvesSolveExcel]

@@DroneEyes estão dois arquivos no zip. O que tem YT no final do nome está a funcionar correctamente porque é o que construí durante o tutorial. Tem que ter Excel para Microsoft 365 ou 2021 para poder usar no desktop. Ou se tiver OneDrive pode salvar os arquivos lá e abrir no Excel online que vai funcionar.

[@NathanAshe-xj7zv]

Trying to do this with 37 categories with 3-6 subcategories each, while adding another row for "titles" with ~5 titles per subcategory was not as enjoyable as the video. A great video all the same, appreciate the lesson Celia!

[@CeliaAlvesSolveExcel]

You survived and learned! So glad that you made it! 💪🔥

[@eduardomunoz2764]

Excelente video, gran explicación. Gracias Celia, saludos

[@CeliaAlvesSolveExcel]

De nada, Eduardo. Gracias por asistir y oferecer tu feedback. 🙏

[@fati4349]

Thank you for the amazing explanation. I would like to know how can I create the same data but I can start by choosing the city and give me the country and the same time if I chose the country I can have the cities belong to. Please answer my question, and I appreciate it ❤

[@CeliaAlvesSolveExcel]

@@fati4349 one option could be to create a dropdown list that has both the country and the city combined separated by a delimiter that will not exist in the country and city names. Example: Toronto > Canada. With Excel 365 or Excel for the web, when you start typing the country or the city, it will narrow down the options available for what your typed. Then, if you need the country and city separate in two columns, use formulas in separate columns to extract the text before and after the delimiter ( ">" in the example). I hope this helps.

[@markp8600]

Magic!!! Great video Celia 😎😎

[@CeliaAlvesSolveExcel]

Thank you, Mark. Glad that you enjoyed it!

[@kevinnguyen5698]

Magic! Do you have a video for ones that would have up to 10 subcategories with some subcategories having the same cell names in different main categories.

[@CeliaAlvesSolveExcel]

I do not. But I've dealt with something similar. Please check my presentation to the event Excel Virtually Global in 2023

[@CeliaAlvesSolveExcel]

kzbin.info/www/bejne/o5_EnGegrJyaq5Ysi=K8we5cgg_f7myw9a around min 49:40

[@markbuys5726]

hi, Thanks for the info is it possible to do this in reverse? example, By selecting a town , you'll get a city , state , country the continent to auto populate

[@CeliaAlvesSolveExcel]

Yes. For that, you still need a list with all the towns and corresponding states and countries. Then, if you'd like, you can set the first dropdown for the user to select the town. In each of the other columns, you can have a formula with VLOOKUP or XLOOKUP to populate the state and country.

[@ExcelUpNorth]

Very helpful video! I am a big fan of Named Ranges as well 😁

[@CeliaAlvesSolveExcel]

Thank you, Darryl!

[@Mohamed.GadAllah]

Thanks a lot, Queen Celia for this amazing video, please, I've 4 columns in a table, and each column has several subcategories, please, how do you apply your technique for the 4 columns compared to your video with 2 columns? Thanks

[@CeliaAlvesSolveExcel]

Hi, Mohamed. Please watch this video with this technique simplified: kzbin.info/www/bejne/i2S6n4Bof6iljMUsi=nuPA4IgpLboih_WK

[@georgeww600]

Hi Celia, were you referring to columns when you should have said Rows?

[@CeliaAlvesSolveExcel]

That could have happened. Which moment in the video are you referring to?

[@courtneygeorgie5758]

Thank you so much for this Celia! I do have 1 part I'm struggling with - I got all the way to the conditional formatting and have triple checked my formula, however the subcategory cells stay red after correcting the drop down category and do not return to unfilled. Is there a way to fix this? Thanks a bunch!

[@CeliaAlvesSolveExcel]

Hi, Courtney. Conditional Formatting applies a format based on a condition. In this case, it is painting the cell in red if the values selected in the two columns are not a valid pair. You have to clear or amend those values manually. Conditional Formatting does not clear the cells from invalid values automatically. To do that automatically, you would have to use vba.

[@ABellaLuna]

Wonderful - but do you have a way to make multiple dependencies? In this example Category is selected from a list, Subcategory is dependant on what is selected in list. But what if you want to keep going with multiple sub categories? e.g. I select a service, then in sub category 1 it shows me where that service is available, sub category 2 is companies offer that service on that location, sub category 3 is if we have a current contract with the vendor selected, and keep going for a few more.

[@CeliaAlvesSolveExcel]

You can create as many subcategories as you want with repeating a similar reasoning. Your table needs to have more columns with all the combinations possible. For the 3rd option selected for example, the category would be concatenation of the first two values selected. I have a ready-to-use file that takes up to 3 columns in the items list. solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/

[@CeliaAlvesSolveExcel]

Although in your case, it seems that not all would be a subcategory in the same table. For example indicating if there is a current project with the selected client would probably be a column with a formula looking up the client in your list of projects. This list is separate from your list of services.

[@gorga1709]

Hello Celia, this is so helpful, thank you very much!, i want to know more about dependent dropdown lists, especially making multilevel dropdown lists maybe 4 to 5 levels. Do you have the materials? I will be very happy if I can get it

[@CeliaAlvesSolveExcel]

Hi! :) I have a ready-to-use file for 3 columns of dependent dropdown lists that you can get here: solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/ For more levels, we need to repeat the process by adding the extra supporting lists.

[@CeliaAlvesSolveExcel]

If you are going to build that many levels, I recommend you watch this video by Wyn Hopkins who found a way of simplifying this method: kzbin.info/www/bejne/i2S6n4Bof6iljMU

[@gorga1709]

@@CeliaAlvesSolveExcel thank you

[@ClaudeBalleux]

Very helpfull! Thank you!

[@CeliaAlvesSolveExcel]

Glad for that. Thank you, Claude!

[@olanrewajubello5881]

Thank you very much for sharing your expertise

[@CeliaAlvesSolveExcel]

You're very welcome! Thank you for stopping by. Glad to help.

[@louisivara498]

Appreciate this so much. God bless your great heart

[@CeliaAlvesSolveExcel]

You're welcome, Louis! Good luck in your projects.

[@johnschroeder2795]

I like the examples!

[@CeliaAlvesSolveExcel]

Thank you :)

[@ElizzaLoren]

If subcategory is not unique, what formula should I use? Please help.

[@CeliaAlvesSolveExcel]

Do you mean you then have a sub-subcategory? You would have to repeat the same reasoning. In the video description there is a link to a page where you can buy a file that has another level of dependency. The file is ready to use you only need to fill in the data. I hope it helps. solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/

[@mestrecris1]

Magic! Repliquei a explicação com um tema do meu interesse. Muito obrigada

[@CeliaAlvesSolveExcel]

Excelente, Ana!

[@nurfareehabintiahmadfisol7223]

Very helpful and clear !!!!

[@CeliaAlvesSolveExcel]

Awesome! Glad that it helped. And thank you for your feedback. :)

[@davebickers7209]

Magic. Very impressive. Which link has the practice file?

[@CeliaAlvesSolveExcel]

Hi, David. Sorry for the mistake and thank you for asking. The link is now available. Glad that you enjoyed the tutorial till the end. 💪👌🔥🏆

[@ggbin8092]

I'm using google sheet, and I'm stuck at 'CategoryInRow'.

[@CeliaAlvesSolveExcel]

Check the video I mention in my reply to @gorga1709. It shows a simplified version of this method that might be compatible with Google Sheets.

[@carot2003c]

You are genius!

[@cammog87]

Hi thanks for your video sorry if this comment appears twice. I have a question can i draw information from 2 seperate dropdown lists and combine them into one list with selection from both lists?

[@CeliaAlvesSolveExcel]

Not sure i understand what you mean. Are you referring to having a third dropdown where the options depend on the values chosen for the first two columns? Is that's the case, check the link in the video description. I have ready-to-use file for that. Basically, you repeat the same reasoning where the category is composed of list of all possible pairs of columns 1 and 2 concatenated.

[@cammog87]

So i have 2 drop down lists both with thier own information but i want to be able after first selection in thier own individual drop downs i want to be able to draw both sets off information into 1 seperate droplist. Is this possible.

[@CeliaAlvesSolveExcel]

@@cammog87 I am still unsure of what you mean. It might be helpful if you provide an example. Did you check the link I mentioned on my previous comment?

[@CeliaAlvesSolveExcel]

Also, please see if this video helps: kzbin.info/www/bejne/mJKtf5ycj792Y6ssi=2KmM1x8wR0uXBbdf

[@cammog87]

So the purpose im using it for is not for what you are using it for in your video example but i have use your example to get me to the point where i am at. So i have one 2 tier drop drop list. I want to create a second drop down list that is the same but with seperate information to the first. Once i make a selection in both lists is there a way to draw from both different list after i have made a selection in them to put them into its own seperate list createing a new list with choices from the 2 seperate lists. I know this is hard to explain im just trying to see if its possible in excel

[@baterdenepurevsuren7687]

hello, my # is not working :( how to fix it.

[@CeliaAlvesSolveExcel]

Which version of Excel do you have? It needs to be Excel 2021 or Microsoft 365.

[@adamtabor6754]

No exercise file.

[@CeliaAlvesSolveExcel]

Please check the video description.

[@Picla_Peremohy]

Wonderful video Celia! Thank you for putting it together for us. Also, thank you for mentioning Ukraine in the Europe category ❤ ❤ ❤ Is there a reason for not including a space between "South" and "Africa"? Would that break the solution? I'll test when I get off this tablet and thought I would ask. Again, thank you for your giving nature to take the time to share your knowledge with the community.

[@CeliaAlvesSolveExcel]

Hi, Jim! Glad that you enjoyed the video. South Africa without space was a mistake. The ability of using spaces is in fact an advantage of this solution compared to other traditional ones. In the video description there's a link for a ready-to-use file in case it interests you. Good luck! :)

[@Picla_Peremohy]

@@CeliaAlvesSolveExcel Thanks Celia. This is so good to know that spaces are allowed and a great feature of this solution. 1000🌟

[@SishGupta]

THANK YOU!!!!

[@CeliaAlvesSolveExcel]

You're welcome! Check a simplified version of this technique on Wyn Hopkins channel.

[@roshanjacob5894]

its quite confusing when its in subcategory

[@CeliaAlvesSolveExcel]

It is. Check Wyn Hopkins channel. See comments for the link. He created an easier solution.

[@louisvanderhorst4022]

Terriffic solution Thanks very, very

[@CeliaAlvesSolveExcel]

You're very welcome. Glad that you found value. :)

[@shravanidarekar132]

TOOOOO Lengthy ....lost focus

[@Colin-Fenix]

Seems outdated and complicated compared to many other solutions to this problem.

[@CeliaAlvesSolveExcel]

I only know one solution better than this one that was inspired in this technique and then improved. Check Wyn Hopkins KZbin channel for that one. A solution that uses very recently added Excel dynamic array functions cannot be so outdated. :) Complex, yes, but any pervious solutions I've seen were not as dynamic as this one, allowing to create as many lists of subcategories you need and with space characters in the category name. Depending on your case, you may not need this level of flexibility.

[@shravanidarekar132]

Toooo Boring same thing to many repetative

[@roshanjacob5894]

its quite confusing when its in subcategory

[@CeliaAlvesSolveExcel]

I know. Two suggestions: - check Wyn Hopkins KZbin channel and find his last video about dependent dropdown lists - he simplified my technique. - you can buy a file that I prepared with all set up where you only need to enter your data: solveandexcel.ca/dynamic-dependent-dropdown-lists-in-excel/