So I need to identify which user could each manager have access to. The tricky point is that a Manager can have an Individual that can be Manager also. So he could have access to the Individuals of his team members Managers.
What I need in order to filtering correctly is the green column:
I spent some time trying to come up with something unique based on your existing layout, but kind of hit a dead end. The open ended number of levels kind of makes it tricky. However, I approached the problem a little differently.
I still have a user sheet with users and their associated title in the company. But I also created a PositionAuthority table that lists all of the positions, the direct manager title one level above, and then a comma delimited list of manager titles that have authority over that particular position. This made it a lot easier to construct complex open ended hierarchies.
First I gave each user a title in a User table:
Then I created a PositionAuthority table that lists each position, the direct manager title above them, and a list of all managers that have authority to that position.
Back in the User table, I have a group of columns that take the user’s title, relates to the position authority table, gets the direct manager title, then uses that direct manager title to find one or more direct managers for a particular user.
Then I have another group of columns to determine all of the users a manager has authority over. This is done by creating a relation between the user’s title and the Authority List in the Position table. Then it looks up all of the matching individual position titles. Then it creates a relation linking those titles to the user titles.
So in the end, everything is dynamic as long as each user has a title and the Position table is updated with all titles and authorities. And it’s also much easier to maintain in the long run. A user can switch titles and the granted access will update automatically across all users. Also, a position can be placed under a different manager title, or access authorities can change and it will update for all users. Hopefully that all makes sense. If I were to do anything differently, I would probably include Row ID’s or Unique ID’s for each position title and then assign to each user the title ID instead of the title name, and also write the ID’s into the authority list instead of name. This allows a title to be renamed in the future without causing conflicts. I would have done it in this example, but it would have been harder to explain and understand.