Using the Index(Collect) function to produce multiple results

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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.