Join Collect Formula with Contains?

Devon Eddy

Trying to figure out the following formula but am having trouble getting it to work:

=JOIN(COLLECT({GHL Maintenance Range 1}, {GHL Maintenance Range 2}, CONTAINS([User Email]@row), ", "))

Basically trying to a join collect which is simple enough, but I want it to count if the user email is Contained in a cell where there are also multiple other contacts.

Appreciate any help!

Kelly Moore

Hey @Devon Eddy

In the formula above, the CONTAINS function is not complete. The range (or where to search) portion of the formula is missing. In your formula, it should have been @cell. =JOIN(COLLECT({GHL Maintenance Range 1}, {GHL Maintenance Range 2}, CONTAINS([User Email]@row, @cell), ", ")) But even correctly written, this will not work.

CONTAINS will not work in a multi select Contact column. You can find more info here and here

You need to use either FIND or HAS

=JOIN(COLLECT({GHL Maintenance Range 1}, {GHL Maintenance Range 2}, HAS(@cell, [User Email]@row), ", "))

cheers,

Devon Eddy

Hey @KDM Thanks for the guidance there. Still having an issue though. I tried working it out with both Find & Has and am getting "Incorrect Argument Set"

Here's the full formula if that plays a factor at all: =IF([Account Primary?]@row = "No", JOIN(COLLECT({GHL Maintenance Range 1}, {GHL Maintenance Range 4}, [User Email]@row), ", "), IF([Account Primary?]@row = "Yes", JOIN(COLLECT({GHL Maintenance Range 1}, {GHL Maintenance Range 2}, HAS(@cell, [User Email]@row), ", "))))

Let me know what you think

Kelly Moore

Hey @Devon Eddy

Have you tried building your formula one IF at a time to determine where the problem is. Your Range4 - is that also a multiselect contact column? Also, as a double check, please verify your cross sheet ranges are the columns you expect them to be.

On your target sheet, what column type is this formula in?

As you continue to build formulas in the future, consider re-naming your cross reference ranges with your actual column names vs the generic range number of smartsheet. Doing so will help both you and the community understand the ranges being referenced in formulas.

Devon Eddy

That's a great tip.. Never thought to rename those but that would definitely help.

I did separate out the if columns and the first one does work. (It is a single select contact field).

Here's the updated formula references, Can't seem to get this to work

=IF([Account Primary?]@row = "No", JOIN(COLLECT({GHL Maintenance Location ID}, {Primary Client Email}, [User Email]@row), ", "), IF([Account Primary?]@row = "Yes", JOIN(COLLECT({GHL Maintenance Location ID}, {Secondary GHL Accounts}, HAS(@cell, [User Email]@row), ", "))))

Kelly Moore

The row that you're entering the formula in - is the [User email]@row found in the {Secondary GHL Accounts}? Is it possible to get screenshots?

Devon Eddy

Hey @KDM Here's two screenshots that should show everything:

1. Sheet where the formula is: https://www.screencast.com/t/u2kRvhtVU
2. Reference Sheet: https://www.screencast.com/t/M12cIvrveLK7

Kelly Moore

LOL, well maybe it doesn't show me everything.

I suspect that you have a combination of email addresses and contact names - I can see some single initials in your email column, as well as contact icons. The row that you're currently on - is there an exact match of that name on the other sheet?

It looks like you're doing a lookup to get your Yes/No in the [Account Primary] column. Consider adding an IFERROR to that lookup formula to prevent the errors. This may cause problems in your current column

I