Using a formula to assign multiple contacts

MeganJones

Good morning!

I am having a difficult time using a formula to assign multiple contacts. I have the checkbox selected to allow multiple contacts, but it seems as though when it comes to a formula, the cell is only seeing it as text.

Example:

Cell1 - megan@test.com

Cell2 - jones@test.com

Formula: Cell1 + Cell2

This then just creates a new contact that's called megan@test.comjones@test.com

Is it possible to use a formula to assign multiple people in the same cell?

Paul Newcome

Unfortunately it is not currently possible to use a formula to output multiple contacts even if the option is selected to allow multiple.

Paul Newcome

Unfortunately it is not currently possible to use a formula to output multiple contacts even if the option is selected to allow multiple.

MeganJones

@Paul Newcome thanks for the quick response! I am definitely disappointed to hear that... I guess that means that the only way that assign it to multiple people is if I manually change the cell? And since I am using a column formula, that's impossible...

Paul Newcome

Unfortunately you are correct. The only other option would be to have a single select populated by a formula or left blank and then have a multi-select that is manual for those rows where the formula left the single select blank.

Giacomo Crucinio

Hi,

I was just trying to do the same thing, as I wanted to send a notifications to multiple contacts, but fetching the contacts form another sheet using the COLLECT formula and sheet references.

Any update or thoughts?

StevenBlackburnMBA

You could actually have a separate worksheet with a list of contact names and use an INDEX/MATCH formula in the cells where you want the multiple contacts to appear referencing the separate worksheet with the contact range. If set up correctly, the index match formula will pull in both contacts and all notifications can be sent to them as they appear in the cell.

We use this in our organization and it works fantastically.

Paul Newcome

@StevenBlackburnMBA This only works if the multiple contacts are already in the same cell in the reference sheet. You can't combine multiple cells from the reference sheet into a single cell via formula and have working contacts.

StevenBlackburnMBA

@Paul Newcome - yes agreed. What we do is set up the reference sheet as a contact management sheet and when we need to change contacts, do so from the reference sheet manually through a help desk ticket. We have someone managing the one reference sheet so the “system” or main sheet can function properly. Great point!

Paul Newcome

@StevenBlackburnMBA So it sounds like it isn't very dynamic or scalable? Typical use cases I see for something like this would be selecting multiple "Roles" in a multi-select dropdown and pulling in the appropriate contacts or grouping contacts together on parent rows type of thing.

It works if you are only pulling a single contact, but dynamically joining multiple contacts together is not possible unless your reference sheet contains every single possible variation of combinations.

So, It is not currently possible to use a formula to populate multiple useable contacts within a cell unless those contacts are first manually populated in a single cell somewhere else.

StevenBlackburnMBA

Definitely. Luckily for us, our contacts don’t change often… only if an external contact changes for the alert we need to send out. So the need for dynamics doesn’t affect us, but it would if we needed to save columns to avoid hitting the 400 limit. Definitely an improvement that could be made. I wonder if this has been asked for via suggestions already?

This seems to come up often!

Corey W.

I think I may have found a workaround for this. It appears that DataMesh will return a single cell with multiple contacts when they are fed from functions. For my use case I have a sheet that collects form entries including an email address. These are grouped by department using a join/collect function in another sheet to create a contact list for each department. Smartsheet will NOT treat these as contacts as described above. But I setup a DataMesh onto a second sheet using the the department as a lookup and the concatenated emails are treated as contacts. Which I use for a Dynamic View.

Paul Newcome

Another community member has put together a post with options for this to include a DataMesh option that doesn't require a second sheet.

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column