Forum Discussion
Solved
Using named range with FILTER function in data validation list (dynamic dropdown)
Hello,
I am trying to use a named range as a data validation list but unsuccessfull do far. The named range is created using Excel's Name manager under Formulas Tab. The named range has a formula that filters a specific column of a table based on two criteria (AND condition).
Here is the main table called "Employees" (see tblEmployees.jpg). It is located in a sheet called "List of Employees"
In another sheet called "Employee Leave Tracker", I have a table called "Leave Tracker". The structure of the table is shown in image tblLeaveTracker.jpg
The user selects "Unit" and "Area" (dropdowns using data list validation are already created), but in the thrird column (Employee Name), I want to display only the list of employees that belong to specific Unit and Area selected in the first and second column. For this, I used a simple filter function:
FILTER(Employees[Employee Names],(Employees[Unit]=LeaveTracker[@Unit])*(Employees[Area]=LeaveTracker[@Area]),"Not Found")
This formula works just fine. I can see the correct results when I use this formula in a cell outside the table. However, I want the dropdown in the third column (Employee Name) to be dynamic based on selection in the first two columns, so I thought of creating a named range using the above "FILTER" formula. I created a named range called "lstEmpNameDynamic". (see Named_Range.jpg)
However, When i try to use this named range in the Data Validation List source, I get an error (see Error.jpg)
How can I solve this? I need the dynamic dropdown to work for each row of the Leave Tracker table.
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.
RockB115you can use the OFFSET approach. I find it pretty useful when the workbook is not very large. See screenshot of the formula below:
lstEmpNameLT: =OFFSET(Employees[[#Headers],[Employee Names]], MATCH(LeaveTracker[@Unit]&LeaveTracker[@Area],Employees[Unit]&Employees[Area],0),, SUM(--(Employees[Unit]&Employees[Area]=LeaveTracker[@Unit]&LeaveTracker[@Area])),)The formula creates an array of Employee names based on Unit & Area (Concatenated).
See the file attached for more details. With this you have a three-level cascaded drop-down.
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.
