Show Multi-Contacts with a JOIN(COLLECT(...) formula -- working, but not showing as contacts

Kayla

I have a Directory Sheet ('Sheet 2 -- Directory': https://app.smartsheet.com/b/publish?EQBCT=a3e35afe3e02404692195182d00936a4) with a list of Teams, Departments, and Contacts.

I want to pull the Contact(s) into another Sheet ('Sheet 1 -- Pull Into Sheet': https://app.smartsheet.com/b/publish?EQBCT=ddb79d0ea9ae43f28f0af7adccb5decf) where the Department matches (if there is no Department listed, it will default to the Team Contact).

I am using a JOIN(COLLECT(.. formula because the Department is a multi-select column since more than one department may be assigned a Task.

Here is my formula: IF(NOT(ISBLANK([Departments Involved]@row)), JOIN(COLLECT({Contact}, {Department}, HAS([Departments Involved]@row, @cell)), ", "), IF(ISBLANK([Departments Involved]@row), JOIN(COLLECT({Contact}, {Team}, HAS([Primary Team]@row, @cell)), ", ")))

It WORKS, but it does not bring the contacts in as contacts. They are brought in as text strings. The only way I can get it to work to bring in multi-contacts as contacts is to use an INDEX/MATCH formula, which I am not able to do with the 'Departments Involved' column being a multi-select column.

From my research, this isn't possible. Crossing my fingers someone has a solution or a suggestion on how to work around this.

If you want a copy of these 2 sheets to play around with, send me your email and I'll share a copy to you.

Thank you!

Paul Newcome

Your research is unfortunately correct. At this time you can only use an INDEX function or use an automation to automatically assign people to be able to get multiple USABLE contacts into a single cell other than by manual entry. Please feel free to Submit a Product Enhancement Request when you have a moment.

Bassam Khalil

Hi @Kayla

Hope you are fine, if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Paul Newcome

@Bassam Khalil Are you able to share how you use a formula to output multiple usable contacts into a single cell?

Bassam Khalil

Hi @Paul Newcome 

Please check the following simple test sheet:

formula to output multiple usable contacts

Paul Newcome

@Bassam Khalil Yes, but that is a direct cell reference. It will not work in this particular case. You can also use an INDEX function, but again... That does not work in this case. I am curious what you had in mind for this particular thread.

Bassam Khalil

@Paul Newcome 

Yes this the first time i saw such question, before i tested that case for direct formula like in the published sheet, and now am traying to check if it's can work in cross sheet reference. it's a new challenge and maybe we can cooperate to find if it's possible. for that i asked  @Kayla to share her sheets to save the time in creating the same case.

Paul Newcome

Ok. So I am honestly kind of excited (total nerd moment) about this and will be creating a new post detailing it, but I FINALLY figured it out thanks to a solution for a different issue.

=JOIN(INDEX(COLLECT({Reference Sheet Contact Column}, {Reference Sheet Department Column}, HAS(Department@row, @cell)), 0), ", ")

Kayla

@Bassam Khalil - I just shared the copy Workspace with you. If you can figure it out, I will be so excited.

@Paul Newcome - First, thank you for your quick reply! I added in the formula you have and it gave me the contacts as a text string in the same way and not a contact. See here added in:https://app.smartsheet.com/b/publish?EQBCT=ddb79d0ea9ae43f28f0af7adccb5decf.

Can you share a copy of the sheet where you got it to work and maybe I can figure out how to replicate it for this sheet?

Paul Newcome

I swear it was working in my sheet, but I accidentally deleted the wrong column when cleaning everything up to publish it and share links and now have to try to redo it. Ugh. Just my luck.

Paul Newcome

So... False alarm. I'm sorry. I must have run into a one-time glitch or something because now I can't get it to replicate again. 😣

Bassam Khalil

@Paul Newcome

So it's possible to be done. I will try your formula and check. I hope it will work again.

Paul Newcome

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

Bassam Khalil

@Paul Newcome

Unfortunately.. I got really excited when I found out you made it. I hope that we will find a solution to this dilemma in the future.. Maybe a new update launched by Smart Sheet. Because it is an issue that deserves attention.

Paul Newcome

@Bassam Khalil I am still a little excited about it. Obviously there is SOMETHING there that is allowing Smartsheet to recognize the multiple contacts, even if it is temporary. Maybe if they can dig into that particular "glitch" it can help lead to making it a permanent thing.

Bassam Khalil

@Paul Newcome

Yes I'm sure of that, let's keep researching and update each other through this post I'm very optimistic that we'll come up with a good solution and maybe discover some useful subtleties by continuing to try.