Formula to Pull Data from Source Sheet if Criteria Matches a Name

Frank S.

Background: I have a sheet with a list of users assigned to specific tasks; users may be assigned multiple tasks. The users are in a contact column, and I have created a column that contains only the names of the users.

I'm trying to create individual sheets for each user that contain the tasks only assigned to them but use the information already in the source sheet.

What I need is a formula that looks at the user assignment column and populates the information from the columns I want. (Sample columns WBS and Title)

I've played with INDEX/Collect/Contains, but it only seems to bring the first match, not all matches, and JOIN does not work since I want the information in separate rows.

The primary reason I want individual sheets for each user is so they can evaluate the tasks only assigned to them.

Any suggestions or thoughts would be much appreciated.

Paul Newcome

I would suggest a row report. You can then filter the report by "Current user" so that you only have to create one report instead of multiple reports.

Frank S.

@Paul Newcome

I thought about that, but I need each evaluator to be able to score each task separately, and using a row report does not allow a score by the individual user.

Paul Newcome

Are you able to provide some screenshots (sample data if necessary) for context?