Can IF and the JOIN(COLLECT) formula be used together?

Hello,

I have a sheet with data. The data is populated from 4 forms and therefore populates in rows. The data needs to then be collated on one Smartsheet, and I am looking to pull through all the quarters into one cell where the quarter is "Q1".

I have successfully pulled through all the "Q1" to show in a cell, by using the formula: =JOIN(COLLECT(....

The problem I am facing is that it is pulling through both "Q1" & "Q2" into one cell, but I only want "Q1". How can I use the 'IF' function here please?

Thank you,

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    In that case (assuming {Range 1} is the QTR column), you would add it as another range/criteria set within the COLLECT function.

    =JOIN(COLLECT({EORMF - Control Range 1}, {EORMF - Control Range 1}, @cell = "Q1", {Client Name}, [Client Name]@row), " ")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers