Open Discussion: Formula to Populate Multiple Contacts in a Single Cell?

Paul Newcome

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.

Paul Newcome

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.

Paul Newcome

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.

Paul Newcome

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? 😫

Paul Newcome

@Genevieve P. WAIT!!!! It happened again!!!!!!!

Paul Newcome

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)

Paul Newcome

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.

Paul Newcome

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.

Michael Baskin

@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.

Paul Newcome

@Michael Baskin I am always happy to help and glad to hear that my posts have benefitted others!

Emilio Wright

Paul Newcome

Aug 19, 2021

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.

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.

Genevieve P.

Hi @Emilio Wright

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

Emilio Wright

@Genevieve P.

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

Genevieve P.

Hi @Emilio Wright

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

kCaldwell

Paul Newcome

Aug 19, 2021

@Genevieve P. WAIT!!!! It happened again!!!!!!!

https://us.v-cdn.net/6031209/uploads/OP70QPQNFEUI/image.png

Hello all!

This formula is exactly what I need. The formula is working for when I am only putting one letter into the Multi column. It also behaves the way you have all been describing when I turn the "Allow Multiple Contacts" setting on and off. However, I believe that the formula is having a hard time picking up on multiple letters in the "Multi" cell. I have not figured out how to put the letters into a rectangular box, as is depicted in the screenshot from this previous comment, and am guessing that could be one pitfall of my setup. I have included a photo of my formula and working sheet to be more clear. I have been racking my brain for days and would really love any possible help. Thank you so much in advance!

Screenshot (2).png

Uploaded Jun 23, 202458.87 KB

Genevieve P.

Hi @kCaldwell

The Multi column in Paul's image is a multi-select dropdown type of column. That being said, even if you adjusted your Text/Number column to be multi-select, the formula won't be able to join together contacts to make them contact type of values. You can use this to bring together the text of the contacts, but not the Contact value (with an email address underneath a name).

Please add your vote to this related Product Idea:

Combine Contacts from multiple columns into a single cell

elizabeth_white

Nov 7, 2022

Allowing contact columns to be combined into a multi-select contact column would make team management and index sheets much more useful.

If Columns 2 through 10 on my sheet are all contact columns, a formula like =Contact2@row + Contact3@row + Contact6@row should produce a multicontact cell, not a text cell.

That would open the door to making a contact table across teams and departments such that you can easily update multiple index groups with a single entry when there is turnover of an individual.

Ex. columns

Group # | Manager | HR | Finance | Sales | Admin | Operations | Marketing | IT | Design

Group 1...

Group 2...

Group 3...

Ex. Formulas

Ideas Index = Sales@row + Marketing@row + Design@row

Budget Index = Manager@row + Finance@row + Sales@row + Operations@row

Website Index = Marketing@row + IT@row + Design@row

---

View Post