JOIN(COLLECT

Hi,

I'm trying to use the join collect formula (example of my formula currently)

=JOIN(COLLECT({Meeting Minutes Topic & AI}, {Meeting Minutes Pursuit #}, [Pursuit #]@row, {Meeting Minutes Updated Date}, <TODAY-7, "-")

Objective: I want the formula to search the meeting minutes sheet (pic 2) to match the pursuit # column and return the Topic column & the Action item column into the highlighted cell below (pic one) only if the pursuit number matches AND the Updated date (on pic 2) is within the last 7 days.


Thank you so much for any help on this one!

Answers

  • Hollie205
    Hollie205 ✭✭✭
    edited 11/03/23

    You have a couple of options depending on how you want your data to show.

    =JOIN(COLLECT({Topic}, {Pursuit #}, [Pursuit #]@row), "-") + "-" + JOIN(COLLECT({Action Item}, {Pursuit #}, [Pursuit #]@row), "-")

    will look like this Topic 1-Topic 3-Action 1-Action 3

    Or you can create a helper column on your reference sheet to go ahead and join the Action Item and the Topic Then use the below formula on your destination sheet.

    =JOIN(COLLECT({Join Topic/Action}, {Pursuit #}, [Pursuit #]@row), "-")

    and the output will look like this

    Topic 1-Action 1-Topic 3-Action 3

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!