Combine Contacts from multiple columns into a single cell

If we cannot dynamically filter contacts to a pulldown this is a much needed requirement!

USE CASE:

We have multiple teams each with their own set of contacts. We want to reference those other contacts into a single column called "Assignee All Row", then reference that column against a project to have a list of who all is working on that project across teams.

Similarly Lookups don't seem to return contacts either. We need to look at a list of pointers to objects like contacts in lieu of strings returned from those objects!

Surprised there hasn't been any traction on this.

Something like having a contact list with different roles linked or using formulas to sync with a project plan. A large project plan with multiple phases and subtasks with different owners many of which overlap and are assigned to multiple roles. Would be incredibly useful to just update the contact list instead of hunting through hundreds of line items.

Especially if these contains items that are linked to Resource Management and Dynamic Views. Easily reassign straight from resource management. Have this as a template and multiply it across thousands of projects.

@Joe Goetschel, you beautiful tropical fish! Your cleverness is going to allow me to remove at least 20 profile data fields and extra columns from multiple sheets and reports! Here are my findings:

1. It works exactly as you described! Additionally:
2. It works when linking from a sheet within my project template (as well as from intake)
3. It works with a JOIN(COLLECT formula, with either "," or CHAR(10) as the JOIN delimiter
4. It even works when there's extra text in the cell besides the email address! 🙌 (ex: "Sam Brown <sam.brown@email.com>")
5. Outside of Control Center, this trick would work in a "save folder as new" template set.

I think the biggest thing to remember is that the original email address column(s) and the formula column are both text/number, and the target/destination column is the multiple contact column. I did have to update my Blueprint Summary columns from multiple contact to text, and then back again, but now new projects are provisioning flawlessly.

Thank you for updating this thread when you found a solution!

Editing to add -

"There are 2 levels of how cell data is "read" Index matching, vlookup and same sheet cell referencing (=), all read the cell content. Linking looks at what I call "Face Value" It doesn't care about the actual content."

This is a great way to think about it, and it's important for heavy Smartsheet users to know. Kudos! I've run into similar scenarios trying to INDEX(MATCH a hyperlink cell, when only the display names comes through, not the URL.

In the realm of large-scale project management, where teams often span multiple departments such as HR, Finance, Sales, IT, and Operations, the need for effective coordination is paramount. Currently, Smartsheet users face a challenge in efficiently managing communications and task assignments across these multidisciplinary teams due to the inability to combine individual contact columns into a single, multi-select contact column.

Example Scenario:

Consider a project with various stakeholders involved:

• Columns: Group # | Project Manager | Finance Lead | IT Support
• Rows: Indicative of different project groups with assigned team members.

The absence of a feature to merge contacts from columns like 'Project Manager', 'Finance Lead', and 'IT Support' into a 'Key Stakeholders' column complicates team management. A unified column would allow for streamlined communication, easier task delegation, and simplified updates to all relevant team members with a single action.

Use Case:

For a project undergoing a phase transition, a unified 'Key Stakeholders' column could be used to notify all pertinent team members about meetings, deliverables, and updates efficiently. Currently, this requires multiple actions across several columns, increasing the risk of oversight and communication gaps.

Benefit:

Introducing the ability to create a multi-contact column through formulas like =Contact1@row + Contact2@row would significantly enhance Smartsheet's utility by simplifying project communications, improving data integrity, and reducing the administrative burden associated with team changes.

This feature would be a valuable addition for users managing complex projects, enabling them to adapt more swiftly to changes and maintain streamlined communication channels across diverse team configurations.

Would it work with Join/Collect and a Join delimiter of ", " ?

Here's a use case where this came up for us: A RACI matrix with 13 functional areas and a contact under each one; then a separate sheet with a list of tasks and 13 columns for each of the functional areas to indicate who is the R, A, C and I for each task. Notably, there can be multiple functional areas assigned to R, C and I.

Now in that list of tasks we'd like to have four columns, R, A, C and I with the actual contacts for each. So a formula would need to, for each row, examine which of the 13 functional areas have an "R" marked, then do a lookup to see the contacts for those functional areas, and populate them all as contacts in the R column. At the moment we can only bring in their names as text, but not as proper contacts.