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:
    81
    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:
    2,008
    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:
    81
    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:
    476
    Complete different way.
    See attached.

    Attached Files:

    Svmaxcel likes this.
  5. Svmaxcel

    Svmaxcel Member

    Messages:
    81
    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:
    2,008
    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:
    81
    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:
    2,008
    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.
  9. Svmaxcel

    Svmaxcel Member

    Messages:
    81
    This was indeed terrific and I got what i wanted, thanks for your help.

    Had few questions regarding excel file attached in Post#4

    When i open the file and click check data, i get a VBA userform
    1) When I click on Manager or Emp button(without selecting anything from listbox) nothing happens.
    2) When i select Manager name from listbox and click on Manager button, the Search box displays all employee under manager name, however the Emp Name is not filtered.
    3) Emp button does not work.

    What i am looking for is as below.

    I want 2 section to filter the name
    a) when i select Manager name in listbox, Emp Name list box should filter based on Manager Name. So user can select Manager name and Emp name and click on select which will copy the data to the worksheet.
    b) A user can directly type Manager or Emp Name in the search box, which will search for all Manager and Emp Name and list it in box below. user can then click on the Emp Name and select on select which will copy the data to worksheet.

    I am using 2 options, because sometimes the list of Employee is very high and it takes time scroll and select it from list box.

    Note : can we use something like google instant search in option no 2, as in user will type J in the search box and it will automatically search in Manager/Emp Name.

    Userform attached for refernce

    Attached Files:

    Last edited by a moderator: Sep 28, 2018
  10. Svmaxcel

    Svmaxcel Member

    Messages:
    81
    @bosco_yip, I am sorry to bump the topic.
    However it was a but urgent and needed your support, my file is pending just for this part.
    I cannot demand anything from anyone, however its a humble request
    Thanks in Advance.

Share This Page