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

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.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!