Welcome!

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

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

Find more posts tagged with

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Week Number Year to Year
I've got the following formula in a Check Box column to check when something is due in the Next 3 weeks. =IFERROR(IF(AND(WEEKNUMBER([Projected Cleaning Date]@row ) = WEEKNUMBER(TODAY()) + 3, YEAR([Projected Cleaning Date]@row ) = YEAR(TODAY())), 1), "") I have them for 2 weeks, 3 weeks, 4 weeks, and 5 weeks. These stopped…
Use Sheet Summary as cell reference for a different sheet
I'm using salesforce connector to pull my team's hours information in real-time. The Salesforce connector sheet contains sheet summaries that I'd like to use a cell reference for a different sheet. I can't seem to find the best way or formula to do this. I don't want to use a dashboard with report widgets because I prefer…
Issues with IF formula
I have two formulas which work well independently, but when I combine them they don't. formula 1: =IF(YEAR([Joined date]@row ) = 2025, JOIN(COLLECT({Membership Survey 2025 - Experience}, {Membership Prioritisation Survey 2025 - Org}, [Organisation name]@row ))) formula 2: =IF(YEAR([Joined date]@row ) < 2025,…