Open Discussion: Formula to Populate Multiple Contacts in a Single Cell?
Alright... So there have been a lot of questions about using a formula to populate multiple usable contacts into a single cell, and it has always been said that it can't be done.
Well here is a solution that will start to fix that. I was presented with a challenge of populating multiple contacts into a single cell based on a multi-select dropdown column.
Reference Sheet:
Primary.....Contact
a................John Smith
b................Jane Doe
c................Paul Newcome
Working Sheet:
Multi-Select.........Output
a................................f
a b.............................f
a b c..........................f
Formula:
=JOIN(INDEX(COLLECT({Reference Sheet Contact Column}, {Reference Sheet Primary Column}, HAS([Multi-Select]@row, @cell)), 0), ", ")
Output:
Actually output the correct contacts as USABLE contacts into each of the single cells!!!!!! 🤯
Shout-outs:
@Susan Vieira for letting me highjack one of her posts to learn more about using 0 (zero) as the row number in an INDEX function.
@Genevieve P. For the brilliant idea of using 0 (zero) as the row number in an INDEX function.
Comments
-
A little extra info on the post... We knew we could use a direct cell reference
=[Contact Column]@row
as long as the multiple contacts we already populated in that other cell. This also let us know that we could use an INDEX function but again... that required the multiple contacts to be pre-populated. the solution above allows us to join together a list of contacts based on a multi-select column which previously we thought couldn't be done.
@Andrée Starå @Mike Wilday @L_123
We finally have a starting point to the challenge of using a formula for outputting multiple usable contacts in a single cell!
Feel free to add different solutions to this thread as they are discovered.
-
UPDATE:
I was moving too fast cleaning things up to publish and accidentally deleted the wrong column, and now it isn't working for me. UGH!!! I knew I should have taken screenshots first to at least show it was working.
I will provide updates as I can. Sorry everyone.
-
So it looks like it must have been a one time glitch. My apologies for the false alarm everyone.
@Genevieve P. I guess maybe we should delete this post so as to not have it pop up when others are searching the Community? 😫
-
-
Now I just need to figure out exactly what I did in which order. Standby everyone. We may have something here!!!
(At least this time I remembered to grab a screenshot)
-
I have done more testing, and it is a glitch that, while it can be taken advantage of, is not exactly the most user friendly.
The setup in my post is actually incorrect. You need to reference a text/number column that has the email addresses listed in the rows.
The formula goes into the working sheet in a contact column with the box UNchecked to allow multiple contacts. Once you save the sheet you will see that those cells with only one contact have populated a contact and the rest are text strings.
Open the column properties and check the box to allow multiple contacts and save. Now everything is working (even tested successfully with an alert).
This is where the glitch kicks in... Once you refresh the sheet or leave it and come back to it, everything is back as text strings. You have to uncheck the "allow multiple contacts" box, save the sheet, re-check the box, then save again for the glitch to kick back in.
So basically you have to leave the working sheet open without refreshing it. Saving other data is fine, but the minute you close or refresh the sheet, you get reverted back to text strings.
-
Interestingly enough, even though they are showing as text strings in the sheet, those cells that only have one email address listed are still valid for automations.
-
@Paul Newcome I KNEW I WASN'T CRAZY. I've done this as well but couldn't figure out why it would seem to work and then break!!
Thank you for all the work you put into testing Smartsheet things. I can't tell you how many of your posts I've benefitted from.
-
@Michael Baskin I am always happy to help and glad to hear that my posts have benefitted others!
-
I know this post is old, and I am not sure if you will notice this message. However, I am trying to accomplish this same thing. Matching any contact from a multi-select Contact List column, and getting all the respective managers from a reference sheet. This formula works, but provides what you state is the "glitch version" of the result, just a text string, not actual usable contacts. Is this something that should be working or did I do something wrong?
=JOIN(INDEX(COLLECT({Manager}, {Team Member}, HAS([Project Members]@row, @cell)), 0), ", ")
Both columns are Contact List columns set to allow multiple contacts per cell.
-
There currently isn't a way to use JOIN to add multiple contacts together into one cell, it will come through as text as you've found.
Kara has a detailed description of how Contacts work with formulas on this other Community thread, here (scroll down about 10 comments).
Cheers,
Genevieve
-
Thank you for the response. However, I am not sure I follow what Kara posted. Reading the post it seems like it should work because the option I am using is Multi Contact -> Multi Contact, with multi-contact input. Is there something that I am not understanding?
I created a separate question here to explain my full process to see if there is something else that I can accomplish: https://community.smartsheet.com/discussion/87308/join-multiple-contacts-in-one-cell-based-on-contacts-in-multi-select-cell#latest
-
I can see that Paul answered you on your other post. It looks like in your scenario you're Joining together multiple, separate contacts into a Text/Number cell as text values, then bringing that into your other sheet. In this instance they will populate as Text instead of contacts.
There's currently no way to Join multiple, separate contacts into one multi-contact cell and have all the values still be seen as a contact. You would need to have a multi-select cell that already has the values manually selected in it to then map across to your other sheet.
Cheers,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!
