Current User Filter

VillageNut

hypothetical situation. I have 5 work teams and each with 5 members. I have a report that i would like filter by user, and I only want the report to show that users team data.
So if user team 1 user 1 logs on i want him to see all of Team 1's stuff
If team 3 user 5 logs on, i want him to see all of team 3's stuff.

I know i could just create 5 separate reports and share that report with each team member, but in reality i'm dealing with 30+ teams and i don't want to create 30 plus reports.

Maybe i need a completely different approach? Ideas?

Adam Murphy

Create a helper sheet that has each team and one cell with all members in it. In your work sheets, use an INDEX/MATCH formula to bring back the correct contacts for each row (matching the team name?). Then filter your single report on the column with those contacts as Current User. Each person should see rows in which they appear in that field and no other rows. Make sense?

VillageNut

Adam, Like this
Helper Sheet
Team Members
Team A User1, user 2, user 3, user 4, user 5

Team B User1, user 2, user 3, user 4, user 5

Main Sheet

Index Match

=INDEX({HelperSheet | Members}, MATCH(Team@row, {HelperSheet | Members}, 0))

Something like this?

VillageNut

That really didn't format correctly. TEAM is a column and Members is a column. TEAM A is a cell value, and the users are a single value in the other cell

Paul Newcome

Try making the [Team Members] column a multi-select contact type column.