SOLVED

Using named range with FILTER function in data validation list (dynamic dropdown)

Copper Contributor

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"

tblEmployees.JPG

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

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)

 

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)

 

DataVal.JPG

 

Error.JPG

 

How can I solve this? I need the dynamic dropdown to work for each row of the Leave Tracker table.

14 Replies

@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#

Screenshot 2020-12-23 at 07.12.26.png

 

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.

@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?

@RockB115 Perhaps easier to help if you upload your file.

@RockB115 

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.

@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.

best response confirmed by RockB115 (Copper Contributor)
Solution

@RockB115 

That's small sample file

image.png

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.

 

@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!

 

@RockB115 

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.

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.

@Oken . 

OFFSET() returns reference on dynamically defined range, not an array.

@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).

Oken_0-1625920785326.png

See the file attached for more details. With this you have a three-level cascaded drop-down.

 

@Sergei BaklanThanks for the clarification. My bad. I was thinking of something else.

Many thanks to @Riny_van_Eekelen .

This has cleared so many concepts and has solved a long frustrating problem for me. Thanks again !!!

1 best response

Accepted Solutions
best response confirmed by RockB115 (Copper Contributor)
Solution

@RockB115 

That's small sample file

image.png

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.

 

View solution in original post