Using the Index(Collect) function to produce multiple results

Andrew Ryback

Hi everyone,

I'm looking for guidance on an index(collect) formula.

I maintain a master list of independent contractors within Smartsheet. I have a secondary list documenting all independent contractors hired in the current year, which is broken down by project.

In my master list, I want to add a column listing all the projects each contractor has been hired to work. I have a forumula that's spitting back data, but it's only showing me the most recent project each contractor was booked on - it's not listing everything.

Is there a way to manipulate this so it shows me ALL of the projects and not just the most recent?

Here's my current formula:

=INDEX(COLLECT({2024 CONTRACTOR BOOKINGS - project}, {POSITION / RESOURCE}, POSITION@row, {2024 CONTRACTOR BOOKINGS - NAME}, NAME@row), 1)

NOTE: I tried subbing ",1" with ", " "", but that didn't work.

Adam Murphy

Try this: =JOIN(COLLECT({2024 CONTRACTOR BOOKINGS - project}, {POSITION / RESOURCE}, POSITION@row, {2024 CONTRACTOR BOOKINGS - NAME}, NAME@row), ",")

Andrew Ryback

@Adam Murphy - that did the trick! Thanks so much.