What to do when contacts in a cell are higher than 20?

Question

Hello, my request form allows individuals to select teams that will participate on a project.  And for each team, there may be multiple emails associated.  My email list can grow quickly from from a few for some small projects to 50 individuals for larger ones.

I have a datamesh running that copies the cell containing the email list (gathered using a formula) and pasting it into a contact cell.  The contact cell is then used for automation tasks like notifications, but is also used in a shared sheet filter for current user, and is applied to a few dynamic views I've created.

The issue I've seen is when I exceed 20 contacts in a cell (limit set by smartsheet) data is still pasted by datamesh, but it displays as email addresses (not contacts) and does not work with the current user filter.  This means projects are missing from that users dynamic view.

Has anyone found a way around this?

Paul Newcome · Answer

You can leverage the SUBSTITUTE functions ability to target a specific delimiter such as the 20th one and change that to a different character that wouldn't be used in any emails such as a pipe "|". Then you can use the FIND function to locate the pipe to get a character count for everythign before it and use a LEFT function.

=LEFT([Full Email String Column]@row, FIND("|", SUBSTITUTE([Full Email String Column]@row, ",", "|", 20)) - 1)

Then in the next column to get the other batch:

=SUBSTITUTE([Full Email String Column]@row, [First Batch Column]@row + ", ", "")

If you need to expand beyond two separate batches because you have more than 40 contacts, let me know, and we can work those formulas out as well.

electrichead · Answer

Thanks @PittsburghRob, I like that idea.  I'd need to review and see if we could create contact groups that make sense.  Some initial concerns I have are that the work spans the full company and I might capture say 80-90% of the common team combinations, but that I'll still have exceptions or that managing the groups becomes a chore itself.  Still going to consider it more though.

electrichead · Answer

@Paul Newcome see image.  Users pick resource groups in one of these two columns, which are combined to another column (Resource Groups) that is not shown.  I then use the formula in the Resource Owner Email column to collect and join all the appropriate emails.  Datamesh copies the emails and populates the Contact column.  In this example, the one resource group shown generates three user emails to contact.

I think I'd need a pretty fancy formula to break out the columns.  I looked on this a bit yesterday but didn't come across a solution.  Maybe I could count the delimiters, find the position of key ones, and use MID to return the appropriate string?  Goal being, give me a result with 20 or less emails and rinse/repeat across columns until I cover them all.