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 !!!
Riny_van_Eekelen I tried this method, but it only works for single row. If you look at my table "Leave Tracker", I need the dynamic drop down to work for all rows of the table. Unfortunately, the dropdown for "Employee Name" column for rows 2 and beyond wont change irrespective of selections in "Unit" and "Area" columns. Only the first row is functional.
Any suggestions on how this can be implemented to multiple rows?
Drop-down list works with ranges, not with arrays. First in mind is to create such helper range somewhere outside - spill two columns from the table, add transpose filters to each row and INDEX()# them in drop-down list.
- Drop down lists actually work with arrays. The OFFSET() + MATCH()+COUNTIF works well in this case. It creates an array of the items you want to list and you can use them pretty much in the dropdown.
RockB115 Perhaps easier to help if you upload your file.
Riny_van_Eekelen Here is the link to the file: https://drive.google.com/file/d/13Hp6ecu2ttYJTs94UgeCF7fFfv2nhxhP/view
Take a look at the sheet "Employee Leave Tracker". There is a table called "Leave Tracker". I want all the rows of the table to have dynamic filtered dropdown for column "Employee Names". User selects values in first two columns "Unit" and "Area". The dropdown in the third column (Employee Names) should have a dynamic dropdown based on values selected in the previous two columns and this should work for all rows of the table.The master data for all the filtering is located in the sheet "List of Employees" in a table called "Employees"
I was only able to get it working for only one row of the table.
