Multiple Dropdown Lists | Dynamic Arrays | Filter Function

Dependent Dropdown Lists in Excel are pretty handy to know. But there's an interesting problem when you've gotta stack multiple dependent dropdown lists and the source data might add or remove data in the different stages.

This video shows how to make this happen using Dynamic Arrays, UNIQUE and the FILTER functions.

#DependentDropDownLists
#DropDownLists
#Rooster

For a list of my Excel courses at Lynda/LinkedIn:


There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.


My book: Guerrilla Data Analysis 2nd Edition

[eldersnackwrap]

At about 1:33, I had an overwhelming desire to get nekkid. Should've had a warning!

Great video, again. Very useful stuff.

[wayneedmondson]

Hi Oz. Pretty cool. I solved it this way: tblFood in B1:C16. In B25 spill the unique categories horizontally with: For the dropdowns in column F, point the list source to $B$25#. In B26, enter: =FILTER(tblFood[Detail], tblFood[Category]=B25, "No Match") and copy right to E26. This will spill each filtered drop list source below each category title in row 25. For the dropdown source in column G, use: =CHOOSE(MATCH($F2, $B$25#, 0), $B$26#, $C$26#, $D$26#, $E$26#) which will effectively pick drop down list 1, 2, 3 or 4, depending on whatever the corresponding choice to the left in column F. If you add more detail items within the current categories, they will automatically show in the drop list of each respective category. If you add more categories, then the CHOOSE formula will have to be modified to account for the 5th, 6th, 7th, etc. category. If you think you will need to add more categories, you can even pre-build references to columns $F$26#, $G$26#, $H$26#, etc. into the CHOOSE, as they will be ignored by MATCH until the categories are added. You would have to copy the FILTER formula right, as required to the same corresponding columns built into CHOOSE. It is not totally dynamic with respect to category additions, but could be made semi-automatic with the suggestions above by pre-building enough expansion into CHOOSE as to accommodate the maximum additional possible categories you think might be added in the future. Anyway, that's my idea to solve this one. Hope someone finds it useful. Thanks for the inspiration to tinker and create. Always great things coming from Excel On Fire :)) Looking forward to more in 2021! Thumbs up and Happy Holidays!!

[GrainneDuggan_Excel]

I can't believe you got socks on roosters!

Cool solution for 5 rows. Next step, making the number of rows dynamic

[AjayAnandXLnCAD]

Awesome! 😀 Another great example of the use of Dynamic Array Functions.

[shadrackawunyo]

Very thankful for you posting this. Very productive

[IvanCortinas_ES]

Excellently counted. The rooster has been spilled. Thank you Oz !!

[스텔-co]

XLOOKUP tshirt? lhahaha how many wardrobes does this man have

[MonkEBoy-udkj]

Any versions of this available for those of us whose employers refuse to upgrade beyond Excel 2016 (no =FILTER, no =UNIQUE)?

[FRANKWHITE]

OK! ive added this video to my fav list!!! thanks!!!

[Gogloplex]

So I love this, and the Hashtag function is brand new to me, so thank you so much. BUT I dislike how in your example you have to commit to a finite number of rows (in this case five) and then make matching columns in your "guts" table every time you want to add a new row.

I played around with it a bit and the solution I came up with was to wrap the Filter function in your "Guts" table in a =TRANSPOSE(). That way every time you want to add a new row, you only need to add a new row in the "Guts" table, so both grow vertically instead of one going vertical and the other going horizontal. As well this removes the need for that Index column you added to the left of the Category column. It's not perfect, as I wish Arrays worked within Tables, but it's still pretty slick.

[rajainam]

what if we have 100s of categories, then is there any quick way to just drag and automatically fill or have to manually type filter and add to list for each category?

[leonquenomuerde]

Thanks for your wonder vídeo.
Unfortunately I missed those where you solve cleanig previous selections mentioned at t'he end of this one, and yours is a long list to choose from 🤭

[barttitulaerexcelbart]

Nice solution Oz, !! As for the last seconds of your video, you can use: Circle Invalid Data (part of data validation). However, if you correct the first column, the circle remains, you have to remove this in a separate step.

[enricodecrescenzo]

First of all Oz, you're always fantastic in explaining how to use Excel at its best!
Lots of advantages with these new formulas, I wish I had them 10 years ago.. but looking at the bright side, that's how we have learned hot to be good Excel formulas manufacturers!

One more thing, the guts: I always create a separate tab where there are all the workings, but naming it the guts is so clear and straightforward, I love it! 😍

[patrickschardt]

Just by the title, I know this video will be fantastic (hers was also)

Uncle Oz The Excel Professor is here to teach us magic again

[mattschoular]

That was great. Thanks Oz. I am loving the Dynamic Arrays and the ways in which we can exponentially expand on all the basic tools in Excel.

[realpulsecoin]

Great video and I love the way you present it. Fun, repetition, smart thinking and new stuff, great combination!

Have you thought about doing array formulas into PQ? Dont need to answer. Have a nice holiday!

[mikelennon]

Does anyone knows how to search entire workbook and return all records which match a certain value from a drop down list with or without a Macro?
e.g. if we have a list of many large organization in a worksheet and have a list of all of their employee, first name, last name, phone, email, ( many per organization) in another worksheet.
How one can see the records of all employees in an organization by selecting the organization's name from a list or drop down box, etc. I would appreciate if your insights.

[GeertDelmulle]

Just after 1 minute: I know already: that’s exactly like my problem, the way I solved it, and I even took the Leila-path (BTW: she has an Excel video on that). Now, I’ll watch the rest of your vid.