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.
@rubenmunozverdu7528 Unfortunately FILTER doesn’t return a range, it returns an ARRAY ( subtly different, and key to why data validation won’t accept it )
@@AccessAnalytic you're right, forgot about that detail! Same problem with countifs and sumifs, only work with ranges, not the values inside the ranges 😭
Many thanks, Wyn. As you and others have said, one day this will be even easier. But seriously, we should be thankful for the vast improvements that MS has introduced into Excel over the past few years - not least of which is the various spilled array functions, which have made my work projects so much easier.
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!
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.
@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)
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?
Great video, one small thing I noticed if the List 1 are not in order it does not work to filter correctly, for example: Colour Green Colour Red Movie Terminator Colour Blue The returned spilled array include Movie as well (returning all between first match and last match), will be: Colour Green Red Terminator Blue
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!
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)
I am sucessful at generating a level2 list from level 1 selection. However when i tried to copy the formula into name manager, itsays the reference is invalid. Any idea why?
Not sure sorry. When do you get the reference invalid warning? When you paste into name manager OR when trying to use that name in a data validation list?
@@AccessAnalytic I figure it out, I was using microsoft online 365 and it didn't work. I used desktop version and was able to put the formula as a name.
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
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.
Totally agree. Thanks for taking the time to leave a comment
@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?
@rubenmunozverdu7528
Unfortunately FILTER doesn’t return a range, it returns an ARRAY ( subtly different, and key to why data validation won’t accept it )
@@AccessAnalytic you're right, forgot about that detail! Same problem with countifs and sumifs, only work with ranges, not the values inside the ranges 😭
This is the solution that I was looking for. No more complicated use of helper columns or other unnecessary stuff.
Thanks a lot!🙌
You’re welcome.I I appreciate you taking the time to let me know you found it useful
Many thanks, Wyn. As you and others have said, one day this will be even easier. But seriously, we should be thankful for the vast improvements that MS has introduced into Excel over the past few years - not least of which is the various spilled array functions, which have made my work projects so much easier.
You’re welcome. Yes, lots of incredibly useful updates. Really 365 is a must, so frustrating to be missing out on new features.
Awesome solution.
Cheers!
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!
I agree this should be easier and the entire validation process needs an overhaul
Superb! Thanks.
Cheers!
Great solution Wyn!!
Thank you 😀
Great learning
I appreciate you taking the time to let me know you found it useful
Excellent video
Cheers!
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.
Yep FILTER in validation would solve all this!
FILTER is definitely the choice where possible.
@@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? 🤔
@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)
Not that I’m aware of unfortunately
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?
That's amazing, thank you
Cheers!
Great video, one small thing I noticed if the List 1 are not in order it does not work to filter correctly, for example:
Colour Green
Colour Red
Movie Terminator
Colour Blue
The returned spilled array include Movie as well (returning all between first match and last match), will be:
Colour Green
Red
Terminator
Blue
Yes. Did you watch the whole video. I explain that later.
@@AccessAnalytic oh right, sorry my bad I didn't watch the entire video :)
This is much better
Thanks 😀
super!
Thank you 😀
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!
Glad to help out 😀
You and Mark should do a “Duelling Excel” series, in the style of Bill Jelen and Mike Girvin!
Nice idea
Hi
Can we make this without converting the cell into the tables
If you mean the input list then yes. Let me know if I’ve misunderstood
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)
I can’t quite remember what that was. 9 months ago I came up with this ruclips.net/video/BoAtpZIf_oY/видео.htmlsi=o3x10rt9nbNBiPEQ
Was that it?
I am sucessful at generating a level2 list from level 1 selection. However when i tried to copy the formula into name manager, itsays the reference is invalid. Any idea why?
Not sure sorry. When do you get the reference invalid warning? When you paste into name manager OR when trying to use that name in a data validation list?
@@AccessAnalytic when i paste it into namr manager 😢 . The formula works when i type it into a cell though
@@AccessAnalytic I figure it out, I was using microsoft online 365 and it didn't work. I used desktop version and was able to put the formula as a name.
Glad you solved it
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
Yeah but you have to wrap in indirect
I find “wrapping” in named ranges that bit more robust 🙂