Concatenating multiple fields from SourceSheet into one field in DestSheet

Hello all, I am new to Smartsheet and this community, so I apologize in advance if my question has been asked and answered numerous times already. I just couldn't find my answer. Here it is anyway:

I am trying to concatenate all fields I find in "Note" on "SourceSheet" and place the conatenated string of text in "CumulativeNotes" on "DestSheet" for each row in "SourceSheet" wherever "DestProj = SourceProj".

I have the following formula to grab one field, and it naturally stops at the first instance it finds because that is how MATCH works:

=INDEX({SoruceSheetTable}, MATCH(DestProject@row, {SoruceSheetSourceProj}, 0), 2)

What do I use to collect and concatenate all notes separated by " - " when the "DestProj = SourceProj" condition is true?

Thank you for your help.

Find more posts tagged with

Join

INDEX

MATCH

Accepted answers

Darren Mullen

@NAMELESS

=join(collect({SoruceSheetNote}, {SoruceSheetSourceProj},DestProject@row ),"-")

You'll have to create {SoruceSheetNote} as a cross sheet reference to your source sheet Notes column.

All comments

Darren Mullen

@NAMELESS

=join(collect({SoruceSheetNote}, {SoruceSheetSourceProj},DestProject@row ),"-")

You'll have to create {SoruceSheetNote} as a cross sheet reference to your source sheet Notes column.

NAMELESS

@Darren Mullen

Excellent! Thank you, Darren, much appreciated! Your formula worked as advertised, and I also like its shorthand format.

Darren Mullen

@NAMELESS you're welcome. Thanks for letting me know it worked!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.