EVEN EASIER! Dependent Drop Down Technique in UNDER 9 minutes!

[@KO1967]

Hopefully at some point Excel will allow FILTER inside named range formulas (along with table references) then this all becomes much simpler but thanks for thinking outside the box. This is the easiest solution I've seen to date.

[@AccessAnalytic]

Totally agree. Thanks for taking the time to leave a comment

[@rubenmunozverdu7528]

@KO1967, if we can store lamda formulas in the name manager, could we just create a lambda that includes a filter? Or would that also fail to work?

[@AccessAnalytic]

@rubenmunozverdu7528 Unfortunately FILTER doesn’t return a range, it returns an ARRAY ( subtly different, and key to why data validation won’t accept it )

[@rubenmunozverdu7528]

@@AccessAnalytic you're right, forgot about that detail! Same problem with countifs and sumifs, only work with ranges, not the values inside the ranges 😭

[@DickvanderVelde]

Awesome solution.

[@AccessAnalytic]

Cheers!

[@GeertDelmulle]

Just for the robustness of it all, the technique at the end has my preference. Thanks for figuring this out. Then again: if only MS would allow for array calculations whenever ranges are allowed - that would be the real improvement. If people like you (and me) have to jump through hoops to get the job done, then there’s an other problem. Thanks for the video!

[@AccessAnalytic]

I agree this should be easier and the entire validation process needs an overhaul

[@Quidisi]

I was like, "Why not use FILTER(), but I just learned the hard way that Data Validation does not accept dynamic arrays, but only static ranges. The FILTER() work-around is to utilize it as a helper column, and then name that range, but I like that XLOOKUP() avoids the helper column messiness. I also just realized that I lied to a coworker. Just yesterday I told him that XLOOKUP() cannot return multiple values, and that he needed to use FILTER(). I'm assuming that when using XLOOKUP() the results must be contiguous - but I'm going to play around with this. Thanks so much.

[@AccessAnalytic]

Yep FILTER in validation would solve all this! FILTER is definitely the choice where possible.

[@Quidisi]

@@AccessAnalytic Is there not any function that we can wrap around FILTER to change the filtered results from a dynamic array, to a static range? 🤔

[@rubenmunozverdu7528]

@Quidisi, That's not correct, Wyn's formula with the xlookup:xlookup trick is dynamic and data validation works. In the older versions of excel you can actually use either offset or index to create similar dynamically built ranges giving them names. Exactly like the video's example: you can save in name manager something like =INDEX(A:A,2):INDEX(A:A,COUNTA(A:A)-1) to have a list from A2 to the last formula with content in A (although this quick example would not work if there are gaps in A)

[@AccessAnalytic]

Not that I’m aware of unfortunately

[@nilselmano]

Maybe i am missing something, but for me it is possible to use the filter function to create spilled arrays, and then use the spilled arrays in the validation list with the # operator. that seems easier to me?

[@ziggle314]

Superb! Thanks.

[@AccessAnalytic]

Cheers!

[@DinoDelight]

That's amazing, thank you

[@AccessAnalytic]

Cheers!

[@JuanIArana]

Great solution Wyn!!

[@AccessAnalytic]

Thank you 😀

[@Bhavik_Khatri]

Excellent video

[@AccessAnalytic]

Cheers!

[@vsrinivasan574]

Great learning

[@AccessAnalytic]

I appreciate you taking the time to let me know you found it useful

[@dispirted8]

You and Mark should do a “Duelling Excel” series, in the style of Bill Jelen and Mike Girvin!

[@AccessAnalytic]

Nice idea

[@by_by_by_86]

This is much better

[@AccessAnalytic]

Thanks 😀

[@Hortster]

These dependent dropdown list videos couldn't have come at a better time as I need to add them into a project I'm working on. I appreciate the friendly competition to find the easiest method!

[@AccessAnalytic]

Glad to help out 😀

[@sledgehammer-productions]

Is this the solution for what you weren't able to accomplish around February 2023? Where I was silly enough to think that I could .... And on the question 'what do you think of it?'. I think it's awesome (in Wyn Hopkins voice)

[@AccessAnalytic]

I can’t quite remember what that was. 9 months ago I came up with this kzbin.info/www/bejne/eKCkpaOQfsuSpbssi=o3x10rt9nbNBiPEQ Was that it?

[@rubenmunozverdu7528]

I had to pause at 2:21. What do you mean data validation won't accept table ranges? Even in license versions of Excel you can use =INDIRECT("t[1]") as the "list" for the validation. I know it's a volatile function but you are not calling INDIRECT from a gazillion cells... End of rant, resuming video... xD

[@AccessAnalytic]

Yeah but you have to wrap in indirect I find “wrapping” in named ranges that bit more robust 🙂