Sign in to submit new ideas and vote
Get Started

Combine Contacts from multiple columns into a single cell

Options
13

Comments

  • gilmore0007
    Options

    This is the most important feature for successfully using the dynamic view filters. Without manually having to manage a list of all "viewers"

  • RAllen
    RAllen ✭✭
    Options


    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!

  • RAllen
    RAllen ✭✭
    Options

    @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!

  • Ethan Jensen
    Options

    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.

  • Dan Branchal
    Options

    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.

  • Joe Goetschel
    Joe Goetschel ✭✭✭✭✭✭
    edited 02/29/24
    Options

    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:

    1. All the contact columns, turn to text columns
    2. Only put the text email in each cell
    3. Make your Formula column. Here you are going to add commas in between each email.
      1. 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

    joe.goetschel@scscloud.com

    Save My Contact

    "The only real limitation of Smartsheet is the level of effort required to achieve your goal."

  • Amy.Mizzi.RP
    Amy.Mizzi.RP ✭✭✭✭
    Options

    @Joe Goetschel this is amazing!!! I'm off to do some testing and will report back!

  • Amy.Mizzi.RP
    Amy.Mizzi.RP ✭✭✭✭
    edited 03/01/24
    Options

    @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.

  • Joe Goetschel
    Joe Goetschel ✭✭✭✭✭✭
    Options

    @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

    joe.goetschel@scscloud.com

    Save My Contact

    "The only real limitation of Smartsheet is the level of effort required to achieve your goal."

  • Steve North
    Options

    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.

  • Shawn Clemmons
    Options

    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.

  • RAllen
    RAllen ✭✭
    Options
  • Joe Goetschel
    Joe Goetschel ✭✭✭✭✭✭
    edited 03/22/24
    Options

    Joe Goetschel | Smartsheet Director | SCS CLOUD - Smartsheet Partner

    joe.goetschel@scscloud.com

    Save My Contact

    "The only real limitation of Smartsheet is the level of effort required to achieve your goal."

  • Karim Khan
    Karim Khan ✭✭✭
    Options

    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.