In the attached workbook, I have a list of employees, supervisors, managers, and directors. I need to create a procedure to determine which teams an end user has access to. The security model works like this:
- For access to be granted on a Supervisor team, an end user must have access to all employees under that Supervisor.
- For access to be granted on a Manager team, an end user must have access to all employees under that Manager.
- For access to be granted on a Director team, an end user must have access to all employees under that Director.
In the sample file, the permissions granted to EndUser1 include all employees that report up to:
- Supervisors Sina Loadholt, Amos Trueheart, Toya Hulsey, and Marry Dutcher
- Managers Booker Wilham and Madeline Donnis
- Director Santo Treloar
Also in the sample file, I have a table in H1:I4 that contains static values right now. Using the Director level as an example, this is the result I want to create programmatically and generally would not be available in the source data. I'm thinking I need to generate the list of Directors using VBA since Directors may be added, removed, or changed at any time and the number of directors at a given time can also change. The values in the EndUser1 Access column could be obtained with VBA or an Excel formula. So to determine that EndUser1 has access to Santo Treloar's team, that user is required to have a permission to match every employee reporting up to Santo Treloar. When that condition is met, the value should be set to "Granted." If even one employee was missing from the EndUser1 Permissions list, the value would be "Denied."
I would very much prefer not to employ helper columns or array formulas.
Thank you in advance!
Bookmarks