Cross sheet formula to allow a series of data to be pulled into 1 cell

Nathalie DeAngelis

This is my formula:

=IF(Staff4 <> "", VLOOKUP(Staff4, {Sheet - PTO & Project Staffing Range 1}, 56, false), "")

I need to see all the data in Staffing Range that Staff4 is working on.

Screen Shot 2020-01-24 at 1.33.00 PM.png

Right now, I have it naming the first name in that column. I want it to list all the different names of projects that Kate is working on.

Please help!

Nathalie

L_123

=join(collect(Project:Project,Project:Project,not(isblank(@cell)),Staff:Staff,@cell= Staff4), ", ")+join(collect([Project 2]:[Project 2],[Project 2]:[Project 2],not(isblank(@cell)),Staff:Staff, @cell = Staff4), ", ")

You just need to add your other sheet references for the project, project 2, and staff columns.

Nathalie DeAngelis

Thank you so much!

Nathalie

Nathalie DeAngelis

I couldn't make that work. I'm going to try to explain what I need more concisely.

Here is my result sheet with the current formula which gives me the first project name (on the other sheet) associated with the Assigned to person.

Screen Shot 2020-01-24 at 5.09.29 PM.png

What I need is for the formula to give me all the project names (on the other sheet) associated with the Assigned to person. Ideally, separated by a comma.

Thank you for your help.

Nathalie

L_123

Is the persons name on a single row, or multiple?

Please post the formula you tried to use that is updated with the current references.

Nathalie DeAngelis

Multiple rows, always the same column.

I've tried so many things. None of them worked. DISTINCT, COLLECT, JOIN and whatever else seemed to make sense.

I'm stumped.

L_123

try posting my exact formula on a random empty cell on the same sheet and see if it works. if not we can take a look at some other options.

Please post the formula you have already tried with your other sheet references so I can check for syntax errors.

Nathalie DeAngelis

Hi,

posting the formula in a random empty cell gets the #UNPARSEABLE error message.

=IF(Staff4 <> "", VLOOKUP(Staff4, {Sheet - PTO & Project Staffing Range 1}, 56, false), "")

This formula works but only the first time a project appears with the corresponding Staff. I want my result cell to mention all the projects that correspond to the person/Staff.

Thank you for your help.

N