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 Duplicates

Discussion in 'Ask an Excel Question' started by Svmaxcel, Sep 8, 2018.

  1. Svmaxcel

    Svmaxcel Member

    Messages:
    54
    I have sheet with Emp Names, Managers and scores listed
    I want to use Data Validation.
    So if I select Manager nane, I want the Emp Names to be filtered accordindly and if I select Emp Name their scores shoukd also reflect.
    Attaching file for reference.

    Note : when I use Data validation in C22 for Managers, I am getting Duplicates, but it should not repeat entries.

    Kindly refer to C22:C26

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    1] Formula solution with helper column

    2] See attached file.

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and Svmaxcel like this.
  3. Svmaxcel

    Svmaxcel Member

    Messages:
    54
    thanks for the reply and solution.
    mind blowing...
    I thought of doing this earlier, however, for this, I will have to make a new table.
    so in next month if the manager will change, ii wil have to alter it again.
    what do you suggest.

    Also if 2 employees name are same, then also it might create a issue with data, can we rather match match Manager and Emp Name to reduce the risk.

    Also wanted to know why is Datavalidation giives duplicates entries.
    i mean if a list has fruits as header and has apple, mango, oranges, are repeated in the list, Validation box keeps giving dupliates
  4. Belleke

    Belleke Active Member

    Messages:
    434
    Complete different way.
    See attached.

    Attached Files:

    Svmaxcel likes this.
  5. Svmaxcel

    Svmaxcel Member

    Messages:
    54
    I used it and it worked, how I wanted it to work.
    When I change Manager name, the Emp Name is still there, though it doesnt match.
    Can we do conditional formating for Emp Name to turn Red in case Emp Name is not under the Manager.
    Or
    Can we clear Emp Name, if Manager doesn't match
  6. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    Please give more information by uploading a sample file with data and explanation of the condition.

    Regards
    Bosco
    Last edited: Sep 13, 2018
  7. Svmaxcel

    Svmaxcel Member

    Messages:
    54
    Kindly refer to the file in Post#2

    Data Validation is in C22 and C23.(Manager and Emp Nane).
    They are using table from H4:I12.

    The table means Employee Sam and Kunal report to Manager RAJ and Robert,Kenny,Russel repory to John.....

    Lets come back to validation part

    In C22, if I Select John as Manager, I can select Robert, Kenny, Russel from Emp Name.(C23)

    Lets say I selected John in C22 and Kenny in C23, I get their data and I am done with Kenny.
    Everything works fine here, no issues.

    Now
    if I want to select Kunal from Emp Name, I first have to change Manager to Raj and then select Kunal.
    but Here is the Catch....
    When I select Manager as Raj in C22, the Emp Name in C23 remains the same as it was before till the time I change it..
    I mean that when I change the Manager in C22, then C23 should either become blank or Red.
  8. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    1] This additional requirement need VBA.

    2] Check this attached revised file.

    Regards
    Bosco

    Attached Files:

    Last edited: Sep 13, 2018
    Thomas Kuriakose likes this.

Share This Page