Dec 22 2020 06:21 PM
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.
Dec 22 2020 10:14 PM
@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.
Dec 23 2020 08:16 AM
@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?
Dec 23 2020 09:10 AM
@RockB115 Perhaps easier to help if you upload your file.
Dec 23 2020 09:21 AM
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.
Dec 23 2020 10:27 AM
@Sergei Baklan But does this work for multiple rows?
Dec 23 2020 10:31 AM
@Riny_van_Eekelen Here is the link to the file: EATT_Test.xlsx
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.
Dec 23 2020 11:25 AM
SolutionThat'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.
Dec 23 2020 06:47 PM
@Sergei Baklan Thanks! This solution works. I was looking to avoid VBA since I was planning to upload this file to Onedrive and use Excel Online. This solution just made this possible.
One very small correction in your XLOOKUP formula was to change "L10 to $L10"
Also, the helper table
=Table2[[Unit]:[Area]]
is dynamic but the FILTER formula on the adjacent cell (N9) didn't update as size of helper table increased.
=TRANSPOSE(FILTER(Table1[Name],(Table1[Unit]=L9)*(Table1[Area]=M9)))
However, it was an easy fix. I just dragged down the formula to fill the entire N column. I suspect this somehow made my excel workbook slower. Saving takes a little more time now. But should be OK and everything works as excepted. Thanks Again!
Dec 24 2020 05:55 AM
Yes, FILTER() shall be dragged down if the table is expanded. On the other hand you may drag it with some gap from very beginning. Filter against empty columns shall return an error, I guess CALC. You may wrap formula with IFERROR or simply ignore errors, in helper sheet they affect nothing.
Performance - I don't think it'll be significant impact, approximately the same as you add one more calculated column into your main table.
Jul 10 2021 05:06 AM
Jul 10 2021 05:12 AM
OFFSET() returns reference on dynamically defined range, not an array.
Jul 10 2021 05:44 AM
@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.
Jul 10 2021 05:50 AM
@Sergei BaklanThanks for the clarification. My bad. I was thinking of something else.
Feb 07 2023 12:15 AM
Many thanks to @Riny_van_Eekelen .
This has cleared so many concepts and has solved a long frustrating problem for me. Thanks again !!!
Dec 23 2020 11:25 AM
SolutionThat'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.