Join/Collect across sheets

Hello,

I think I am really close with my formula, but still getting an unparseable error.

=JOIN([Freelancer's name]@row:{Feedback Range}, COLLECT({Feedback Range}, {Audition Status Range}, [Audition Status]@row), "'s ")

I have a source sheet (Feedback) that contains 3 different feedback text, for three different Audition Status scenarios.

When the Audition Status on my current sheet matches the Audition Status in the source sheet, I would like to add the Freelancer's Name (on the current sheet) to the feedback text, with a 's as the delimiter. (The aim here is that an admin can copy/paste this message.)

Images attached to show the two sheets.

Screen Shot 2020-10-26 at 2.30.34 PM.png

Screen Shot 2020-10-26 at 2.29.18 PM.png

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Hannah H

If I'm understanding your process correctly, the second sheet is a reference table that contains the text you want returned, based on the Audition Status.

If so, you actually don't need the JOIN(COLLECT which would join together multiple cells from the other sheet. Instead, you'll want to add the content of one cell with an 's, to the content from the other sheet. To do so, you can use an INDEX(MATCH to bring in the Feedback, and the + symbol to add together the content.

Try this:

=[Freelancer's name]@row + " 's", + INDEX({Feedback Range}, MATCH([Audition Status]@row, {Audition Status Range}))

Let me know if this works for you, or if you have any questions about this!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Hannah H

If I'm understanding your process correctly, the second sheet is a reference table that contains the text you want returned, based on the Audition Status.

Try this:

=[Freelancer's name]@row + " 's", + INDEX({Feedback Range}, MATCH([Audition Status]@row, {Audition Status Range}))

Let me know if this works for you, or if you have any questions about this!

Cheers,

Genevieve

Hannah H

Thank you @Genevieve P that worked. I guess I was trying to make it too complicated!

Genevieve P.

No worries at all, I'm glad that worked for you! 🙂

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.