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.