User Profile
RockB115
Copper Contributor
Joined Dec 22, 2020
User Widgets
Recent Discussions
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.
