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

VLD

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,

Paul Newcome

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), " ")

Paul Newcome

Are you able to provide the exact formula you are using as well as screenshots of the source data (sensitive/confidential data can be blocked out or replaced with sample as needed.

VLD

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

Source Data:

Collected Data:

Paul Newcome

I'm not sure I understand what you are wanting to accomplish. I do understand you just want to pull in the Q1s, but based on what logic? Would you want all 4 of the Q1s displayed?

VLD

Yes, if column QTR = "Q1" then I want them to pull together in the cell, so it will just be -

Paul Newcome

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), " ")

VLD

🤩 awesome, thank you so much

Paul Newcome

Happy to help. 👍️