1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by HeyInKy, Mar 20, 2017.

  1. HeyInKy

    HeyInKy New Member

    Messages:
    3
    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.
  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

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

    Thanks
  3. HeyInKy

    HeyInKy New Member

    Messages:
    3
    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.

    Attached Files:

  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,317
    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

    Attached Files:

    Thomas Kuriakose likes this.
  5. HeyInKy

    HeyInKy New Member

    Messages:
    3
    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!

Share This Page