Join/Collect across sheets

Hannah H

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.

Thank you!

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

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

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! 🙂