I have an Excel file with the following:
A sheet named "org" containing a table called "tblOrg"
"tblOrg" has three columns: "Employee", "Supervisor", and "Manager"
A sheet named "team" with a named range "managerSelection" and an empty table "tblTeam" with only one column: "Direct Reports"
based on the manager's name typed into managerSelection, I need to populate tblTeam with a unique list of supervisors that report to that manager. Ideally, I would like the list to be sorted alphabetically when I write it. I can't pre-sort the table because I will need to repeat this process for employees reporting to supervisors based on the same source table. I need the list to dynamically change whenever a new manager name is selected.
Here is a sample from tblOrg (sample file also attached):
Employee Supervisor Manager
Paul Jon Lori
Bob Jon Lori
Jane Denise Lori
Lucy Denise Lori
Ken Julie Alex
Maria Julie Alex
Bill Cynthia Alex
Ethan Cynthia Alex
Wallace Heidi Kim
Trevor Heidi Kim
Karina Dennis Kim
Erin Dennis Kim
Christine Will Kim
Devon Will Kim
Based on the sample, if I put "Kim" into managerSelection, I should get the list as:
Dennis
Heidi
Will
Thanks in advance
Bookmarks