• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation from table, return list matching 2 values

HeyInKy

New Member
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.
 
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.
Hi,

It is probably better if you can upload the sample file... it is quite hard to give a targeted answer otherwise.

Thanks
 
Ok... this is close... in column [Region] on the "DataTable" - I need the data validation to return a list of the employee names of only those matching the [Level] "Asst Sup" and "Manager" for the corresponding [Region] in column B, per the Employee Table.
 

Attachments

  • sample 2c.xlsx
    13.1 KB · Views: 7
Try,

1] Please refer to the attachment

2] In "EmployleeTable" Sheet, helpers column added in Column D to Column K

3] In "Data" Sheet, Column C will have the data validation for the list of "Asst Sup" and "Manager"

4] In "Data" Sheet, Column D will have the data validation for the list of Employee other than the "Asst Sup" and "Manager"

Regards
 

Attachments

  • DataValidationSample.xlsx
    17.8 KB · Views: 7
That's quite a work around! (I'm not complaining) ...it'll be tomorrow though before I have time to digest and process it, but I sincerely appreciate the assistance!
 
Back
Top