Join Multiple Contacts in one Cell based on Contacts in Multi-Select Cell

Emilio Wright

Hello Community,

Thank you for you assistance in advanced. I will provide you the end result ahead of the post in hopes that it can spark an idea that I might not have thought of before.

Required Solution: Join multiple Managers (contacts) into one single cell based on a list Team Members (contacts) from another one single cell.

We are currently joining the Team Members (contact) from our Project Plans into one Profile Data field. Using this formula:

JOIN(COLLECT(Owner:Owner, Owner:Owner, NOT(ISBLANK(@cell)), [Parent Helper]:[Parent Helper], "Project Team"), ", ")

Parent Helper is a column used to ensure we only gather the cells that are Children of the "Project Team" Parent.

We then want to convert this string back into contacts to allow us to find the Managers for these users within the Summary Sheet. I was able to make this work for one individual user, but when using multiple names in the string, I am unable to convert them into contacts in one single cell. Using this formula:

=INDEX({Team Member}, MATCH([Project Members]@row, {Team Member}, 0))

Once I have all the users as contacts I would like to find the Managers and also list those Managers in one cell as Contacts. Using this formula:

=JOIN(INDEX(COLLECT({Manager}, {Team Member}, HAS([Project Members]@row, @cell)), 0), ", ")

I got this idea from this post, but it doesn't seem to be working as they mentioned: https://community.smartsheet.com/discussion/82311/formula-to-populate-multiple-usable-contacts-in-a-single-cell

Thank you for all the help.

Paul Newcome

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.

Paul Newcome

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.

Emilio Wright

@Paul Newcome Thank you for your input. I notice that the solution that I am asking for can be reach a different way. It seems to have been my fault for not providing you the problem from the highest possible view. I will close this ticket as I was able to find a solution to my actual problem, even though the problem I stated here does not have a solution yet.

MichelleA

@Emilio Wright - Can you please share your solution? I'm looking to take contacts from multiple rows in a single column and combine them into 1 cell so I can use this to notify the team when a specific milestone is met. The project team changes so I'm looking to create a template with this formula so it will pull each new project team.

Andrée Starå

Hi @MichelleA

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Emilio Wright

@MichelleA The problem that I stated in this post does not currently have a solution. However, I did find a solution that would work for my specific circumstance to reach the outcome that I needed. That being said, this part of my project was removed as we chose a different approach.

StevenBlackburnMBA

Hi all - this is actually possible with an INDEX/MATCH formula referencing the contacts from another worksheet. If done correctly, both contacts are pulled -- we use this all the time and have had this set up since Summer 2022. Not sure if an update has been made since, but this is possible now.

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

I wrote back on our other thread.

Thanks Paul - do we know if the suggestion has been polled by the community yet?