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

RockB

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 (Eployee 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:
Excel Formula:
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 dyanmic 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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Using Filter means it may get Non Continuous Cells.
Named Ranges can have Non Continuous Cells but won't work for Data Validation as Source
See this discussion:

I might have a workaround, but it uses VBA not formula, let me know if you're interested.
 

RockB

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Using Filter means it may get Non Continuous Cells.
Named Ranges can have Non Continuous Cells but won't work for Data Validation as Source
See this discussion:

I might have a workaround, but it uses VBA not formula, let me know if you're interested.
Yes, please share your VBA solution
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Ok, here's an example:

but I can see that the example doesn't suit your data structure, but if you're interested in this method I think I can amend the code to suit your need.
Could you upload a sample workbook to a free site such as dropbox.com or google drive & then put the link here?

Note:
One caveat of using macro is when a macro changes/writes something in the sheet it will clear the Undo Stack, so at that point you can't use UNDO.
In this case it happens whenever you put the cursor in the cell with the data validation.
 

RockB

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, here's an example:

but I can see that the example doesn't suit your data structure, but if you're interested in this method I think I can amend the code to suit your need.
Could you upload a sample workbook to a free site such as dropbox.com or google drive & then put the link here?

Note:
One caveat of using macro is when a macro changes/writes something in the sheet it will clear the Undo Stack, so at that point you can't use UNDO.
In this case it happens whenever you put the cursor in the cell with the data validation.
Thanks! 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.

I was only able to get it working for only one row of the table.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Ok, try this:

Note:
I use table in sheet List of Employees as the source of data validation.

How to set it up:
  1. In short, what you should do are:
  2. Create a named range “xName”, refers to a cell, any cell will do.
  3. Create a data validation in B4, choose “Allow > List” then source: =xName
  4. then apply it to column B:D until the last row of the table OR if you plan to add the table row in the future then you can apply the data validation below the table to as many row as needed.
 
Solution

RockB

New Member
Joined
Dec 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Ok, try this:

Note:
I use table in sheet List of Employees as the source of data validation.

How to set it up:
  1. In short, what you should do are:
  2. Create a named range “xName”, refers to a cell, any cell will do.
  3. Create a data validation in B4, choose “Allow > List” then source: =xName
  4. then apply it to column B:D until the last row of the table OR if you plan to add the table row in the future then you can apply the data validation below the table to as many row as needed.
@Akuini This works great! Thanks.

I have another sheet in the same file that I shared called "Employee Ovvertime Tracker". It has the same table structure as "Leave Tracker". I want the same functionality in this sheet too.

Can I just copy the VBA code from the sheet "Employee Leave Tracker" to "Employee OverTime Tracker"? Do I need to make any more modifications? (create a new named range like xName?)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,937
Office Version
  1. 365
Platform
  1. Windows
Yes, just copy and amend all variable value accordingly and just to be safe I think it's better to create a new named range.
Let me know if you problems.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,387
Latest member
Vincent88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top