Concatenating multiple fields from SourceSheet into one field in DestSheet

NAMELESS

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.

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.

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!