Using CONTAINS with JOIN(COLLECT)

scisar

Hello!

I'm trying to add CONTAINS to an existing JOIN(COLLECT) formula, and I can't seem to get it to work.

The existing (working) formula is: =JOIN(COLLECT({RAID Log Title}, {RAID Log Flag}, "1", {RAID Log Category}, "Issue", {RAID Log Top 10 Priority}, [Priority Name]@row), ", ").

The formula I've come up with to add CONTAINS is: =JOIN(COLLECT({RAID Log Title}, {RAID Log Flag}, "1", {RAID Log Category}, "Issue", {RAID Log Top 10 Priority}, CONTAINS([Priority Name]@row, {RAID Log Top 10 Priority})), ", ").

The problem with the CONTAINS formula above is that it doesn't return any results in any of the cells the formula is applied to.

The reason I want to move from the 1st formula to the 2nd is that the cross-referenced sheet now possibly lists more than 1 RAID Log Top 10 Priority in that column, and I want the text of the RAID Log Title to be returned when the Priority Name of the row can be found in the RAID Log Top 10 Priority (not just when it's an exact 1:1 match).

Any help would be much appreciated.

Thanks!

Sara

Nick Korna

Hi @scisar,

Your CONTAINS section of the formula is incorrect, I think. It should be:

...CONTAINS([Priority Name]@row, @cell)...

Try substituting that in and seeing it if solves your issue.

Nick Korna

Hi @scisar,

Your CONTAINS section of the formula is incorrect, I think. It should be:

...CONTAINS([Priority Name]@row, @cell)...

Try substituting that in and seeing it if solves your issue.

scisar

Hi @Nick Korna,

This worked! Thanks so much. I don't really understand the @cell reference, though. Can you help me out with why that fixed the problem?

Thanks,

Sara

Nick Korna

Have a look at these:

https://help.smartsheet.com/function/contains

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

These should hopefully give you some pointers and examples. 😊