I don't have a sample, but can make one if needed... but I think I can describe what I need pretty easily.
On sheet "Data Table" I have a table for data entry and am using data validation and formulas. On sheet "Data Validation" I have another table, "EmpTable", with 4 columns: Region (col. A), Employee (col. B), Level (C), and Category (D) columns. The region column contains a number from 0 to 15, the employee column the names of employees, and the last 2 are ways we categorize and our employees - specifically, the level column has 3 options: Line Staff, Shift Supervisor, and Manager.
On the Date Table in column, column B asks for the Region (validation allows whole number 0-15), and Column D asks for the employee name. Data validation for this column is:
=OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1,1,COUNTIF(RegionColumn,B2),1)
...where RegionStart is a named range =EmpTable[[#Headers],[Region]] and RegionColumn =EmpTable[[#All],[Region]]
This works great and produces a list of all employees the match the region number entered in B2.
The question:
I now need data validation based on B2 of the Data Table (or possibly another cell, but let's go with B2 for now) that the the entered region will return the list of employees that match either one of 2 values in the 3 third column of EmpTable, either Shift Supervisor or Manager. In other words, the drop down list on my Data Table in column C should list the names of all the shift supervisors AND all the managers in the region matching what's entered in B2, but not the Life Staff employees.
On sheet "Data Table" I have a table for data entry and am using data validation and formulas. On sheet "Data Validation" I have another table, "EmpTable", with 4 columns: Region (col. A), Employee (col. B), Level (C), and Category (D) columns. The region column contains a number from 0 to 15, the employee column the names of employees, and the last 2 are ways we categorize and our employees - specifically, the level column has 3 options: Line Staff, Shift Supervisor, and Manager.
On the Date Table in column, column B asks for the Region (validation allows whole number 0-15), and Column D asks for the employee name. Data validation for this column is:
=OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1,1,COUNTIF(RegionColumn,B2),1)
...where RegionStart is a named range =EmpTable[[#Headers],[Region]] and RegionColumn =EmpTable[[#All],[Region]]
This works great and produces a list of all employees the match the region number entered in B2.
The question:
I now need data validation based on B2 of the Data Table (or possibly another cell, but let's go with B2 for now) that the the entered region will return the list of employees that match either one of 2 values in the 3 third column of EmpTable, either Shift Supervisor or Manager. In other words, the drop down list on my Data Table in column C should list the names of all the shift supervisors AND all the managers in the region matching what's entered in B2, but not the Life Staff employees.