Excel Dependent Drop Down List with Tabular Data without Named Ranges

Find out how to create a dependent drop down list in Excel based on a Tabular data set. Instead of having the contents of each drop-down beside each other as we had in the previous tutorial, we now have the contents of each drop-down right below one another. To distinguish which category belongs to which parent, the parent is listed right beside the category. This provides us with a table that has two columns. One for the parent and the other for the content.

We would like to come up with a dynamic formula so that when the user selects a category from the first drop-down (i.e. the parent list), Excel figures out which part of the tabular data-set to include in the 2nd drop-down. This means we need a dynamic formula that always shows the part of the table that has a match for the parent category.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[GATIF]

Awesome, I was looking for this functionality in my excel sheets for many days. Dear you are amazing as your tutorials are easy to understand and very helpful. Please give steps to your tutorials so that anyone who wants to learn step by step excel find it more easy.

[azizniazi]

Our teacher (Leila Gharani) is one of the greatest teachers .


Love you .

[VijayKumar-iwhe]

Madam,
Thank you! Thank you! Thank you so much.
I tried sreching this whole night from last 3days.. finally I got it from you..
Thanks. Keeping doing good job & God bless you!!

[empathic_mimicry]

Hi Leila. I love your videos. The viewers of this should be very grateful of videos like this. I still remember the night when I first used offset match countif within a validation list. Perhaps you have already covered this on another video but if you use offset as an array and add sum infront of it you get a dynamic sum, it's particularly useful for making a YTD model.

[chrisr]

Thank you, this is exactly what I was looking for. Took me a bit to get working since i was pulling from different sheets, but works like a charm now.

[alexanderwhelan]

Hey. Thanks for all the great videos. I`m learning a lot, and seeing new ways to use familiar formulas.

I would love it if you could maybe start using the FORMULATEXT function while you`re doing these as well. Right now I have to wait for you to highlight a cell again to see the whole formula :)

[Dev_Bartwal]

I am addicted of your all Excel videos... big contribution LG thanks❤️

[kent]

Exactly what I'm searching for. Thank you very mush, you are my Saviour!! Great work there, will share with my friends.

[valentinvladov]

You save my life for 1000th time, keep it up with the great work! Love your content!

[emiliorojas]

Hello LG,
Beautiful videos all around.
Thank you!

[projectlez]

amazing - this has really helped and you made it so easy to follow:)

[mohamedbayo]

Thanks a lot, it's amazing and so helpful. Brief and concise.

[gregorythomas]

Thank you... took me a few minutes to get it but you made my day!

[bharatvision]

Exactly what I was looking for. Great video, your teaching is flawless! My job got done.

[esaralmario]

Thanks, i've been struggled for hours, and finally found this video, thank you so much

[harshadpednekar]

This is fantastic, great stuff. THANK YOU very much. you made my excel.

[jamesogle]

Great clear tutorial for what is a great spreadsheet design element. Might want to include a brief explanation on how to get around the Data Validation formula bar not recognizing Table and column names as in the comments below from German Vargas, Carla V and Rene Oldenhoff.

[ahmadrezacheraghloo]

Thank you so much because of your awesome videos..I have a question, what should we do if divisions were not sorted exactly after each other??

[uniedtech]

Life-saver to find this again. A whole lot of help it is, thanks.

[johnborg]

Just Discovered your sight on Excel. I usually follow Mike's. very interesting and I will start following yours too from now on :)