Forum Discussion
Using named range with FILTER function in data validation list (dynamic dropdown)
That's small sample file
Helper range expands automatically if you change the table except spills in column N shall be drag down. Data validation formula is
=XLOOKUP($H4&$I4,$L$9:L10&$M$9:$M10,$N$9:$N10)#It is expandable if take care about absolute/relative references.
Just mock-up, perhaps it could be done more elegant.
RockB115 Lists in data validation need to refer to named ranges. You created a named formula.
Put your FILTER formula in an area outside the view of your work. For example in cell AA1. The FILTER function will spill the matching Employee names to cell AA1 and down.
Now, you can point data validation to =$AA$1#
The # sign tells Excel to take the entire spilled range starting in AA1. Perhaps only one row or sometimes ten rows. Or nothing at all. Depending on the FILTER outcome.
Many thanks to Riny_van_Eekelen .
This has cleared so many concepts and has solved a long frustrating problem for me. Thanks again !!!