Combine Contacts from multiple columns into a single cell
Options
Comments
-
I have wished for this feature for a long time, but my current use case would see so much benefit that I had to post it:
I'm developing a Control Center blueprint for a project toolkit that will be used for thousands of projects every year, so scalability is key. In order to give visibility of the right projects to all the potential resources assigned, as well as to their multiple layers of management, I'm having to use 38 contact columns to gather the contacts with formulas, 38 report filters set to Current User, and 38 metadata fields to sync these resources everywhere they need visibility across the toolkit.
If a formula could combine contacts into a single field, I could accomplish this task with just 2 columns/fields - the savings in my time and in cell linkages and cross sheet references weighing down sheets would be immense. Please add this to the roadmap!
-
This is the most important feature for successfully using the dynamic view filters. Without manually having to manage a list of all "viewers"
-
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!
-
@Olen Ronning (Repeat post?)
Without ways to dynamically build or filter drop-downs this is a must.
We have teams, each with a list of associate contacts. We need to join those lists into an "All contacts" to combine different teams into a list for the same project numbers in different rows; This allows teams can see which projects are actively worked on by other teams an who they can contact.
VLookup against (TYPE CONTACTS) to return a value to (TYPE CONTACTS) doesn't seem to work either. Would be great to have a JOINif(List, Condition e.g. > "", ",") work with these as well to eliminate nuls so joins don't come back as ",,,,,"
The values need to be managed by a pointer or handle rather than returning a list of the string, or manage the contacts as JSON Values in a list... Concatenating, vlookup, +, or any other values of that JSON type should return a list of contacts!
-
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.
-
We have a very strong use case where we would like to concatenate a number of contact fields into a single field so we can provide adequate permissions to the dynamic view we have set up. Either we need a way to concatenate multiple contact lists OR dynamic view needs to allow us to select more than one list for access controls. It appears this conversation has been going on for quite some time - hopefully there is some traction.
-
I got a solution that works I just tested it!
The linking works in core but it's not going to be automated UNLESS you are using Control Center or Data Mesh.
@Amy.Mizzi.RP here is what SHOULD work!
In your intake:
- All the contact columns, turn to text columns
- Only put the text email in each cell
- Make your Formula column. Here you are going to add commas in between each email.
- Example: =[Column1]@row + "," + [Column2]@row
SCC Blueprint:
- You want this to be a link for your profile data (write 1 time MIGHT WORK I just didn't test that)
Profile Data - sheet:
- Make sure the column is set for multiple contacts
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.
"Intake"
"Profile Data - sheet"
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
@Joe Goetschel this is amazing!!! I'm off to do some testing and will report back!
-
@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:
- It works exactly as you described! Additionally:
- It works when linking from a sheet within my project template (as well as from intake)
- It works with a JOIN(COLLECT formula, with either "," or CHAR(10) as the JOIN delimiter
- It even works when there's extra text in the cell besides the email address! 🙌 (ex: "Sam Brown <sam.brown@email.com>")
- 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.
-
@Amy.Mizzi.RP WOOOOO I'm so Happy it worked!!
It will be interesting to see how these function when the new grid gets released! I wonder if it will still read "Face Value" or not!
I LOVE complicated issues so if you have any more send them my way! 🐠
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
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@rowwould 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.
-
The Index Match feature works well for many scenarios. Unfortunately, when it comes to bringing in more than one contact into a Contact List field type it is unable to.
I would love to see an improvement in this.
-
Would it work with Join/Collect and a Join delimiter of ", " ?
-
@RAllen No testing needed! @Amy.Mizzi.RP got this to work!
Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
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.
