Data Validation Duplicates

[Svmaxcel]

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

 

[bosco_yip]

1] Formula solution with helper column

2] See attached file.

Regards
Bosco

 

[Svmaxcel]

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

 

[Belleke]

Complete different way.
See attached.

 

[Svmaxcel]

1] Formula solution with helper column

2] See attached file.

Regards
Bosco

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

 

[bosco_yip]

Please give more information by uploading a sample file with data and explanation of the condition.

Regards
Bosco

 

[Svmaxcel]

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.

 

[bosco_yip]

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.

1] This additional requirement need VBA.

2] Check this attached revised file.

Regards
Bosco

 

[Svmaxcel]

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

 

[Svmaxcel]

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