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

[RockB]

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 (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:

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)

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.

 

[Akuini]

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:

Named Ranges - Non Continuous Cells

Hi Guys, A quick one for you all. I have entries in 3 cells, viz: D6,D7 and G13. I want all of the three to be included in a named range which I will be using as a drop down validation on another sheet. Can this be done.

www.mrexcel.com

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

 

[RockB]

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:

Named Ranges - Non Continuous Cells

Hi Guys, A quick one for you all. I have entries in 3 cells, viz: D6,D7 and G13. I want all of the three to be included in a named range which I will be using as a drop down validation on another sheet. Can this be done.

www.mrexcel.com

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]

Ok, here's an example:

dhee - multiple dependent data validation.xlsm

Shared with Dropbox

www.dropbox.com

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]

Ok, here's an example:

dhee - multiple dependent data validation.xlsm

Shared with Dropbox

www.dropbox.com

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]

Ok, try this:

EATT_Test - RockB -2.xlsm

Shared with Dropbox

www.dropbox.com

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.

 

[RockB]

Ok, try this:

EATT_Test - RockB -2.xlsm

Shared with Dropbox

www.dropbox.com

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]

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.