Using a formula to assign multiple contacts
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?
Best Answer
-
Unfortunately it is not currently possible to use a formula to output multiple contacts even if the option is selected to allow multiple.
Answers
-
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 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...
-
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.
-
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?
-
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.
Certifications:
-Smartsheet 2023 Core Product Certification
-Smartsheet 2023 System Administrator Certification
-
@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.
-
@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!
Certifications:
-Smartsheet 2023 Core Product Certification
-Smartsheet 2023 System Administrator Certification
-
@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.
-
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!
Certifications:
-Smartsheet 2023 Core Product Certification
-Smartsheet 2023 System Administrator Certification
-
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.
-
Another community member has put together a post with options for this to include a DataMesh option that doesn't require a second sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!