Sign in to submit new ideas and vote
Get Started

Combine Contacts from multiple columns into a single cell

Allowing contact columns to be combined into a multi-select contact column would make team management and index sheets much more useful.

If Columns 2 through 10 on my sheet are all contact columns, a formula like =Contact2@row + Contact3@row + Contact6@row should produce a multicontact cell, not a text cell.

That would open the door to making a contact table across teams and departments such that you can easily update multiple index groups with a single entry when there is turnover of an individual.

Ex. columns

Group # | Manager | HR | Finance | Sales | Admin | Operations | Marketing | IT | Design

Group 1...

Group 2...

Group 3...

Ex. Formulas

Ideas Index = Sales@row + Marketing@row + Design@row

Budget Index = Manager@row + Finance@row + Sales@row + Operations@row

Website Index = Marketing@row + IT@row + Design@row

72
72 votes

On Radar · Last Updated

Thanks for sharing this idea and we are always looking for ways to improve formulas. While this is not currently part of our plan, we would like to learn more about the various use cases where you're looking to merge multiple contacts into a single field. Please continue to add your scenario to this post so we can gather more insights.

«134

Comments

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    edited 02/15/23

    Currently Smartsheet is a total mess with formula-generated contacts. Can’t deal with some entries displayed as name and others as email. This is a highly frustrating situation requiring awkward and laborious workarounds.

  • A.J.
    A.J. ✭✭✭✭✭

    This would be really helpful.

  • Thanks for sharing this idea and we are always looking for ways to improve formulas. While this is not currently part of our plan, we would like to learn more about the various use cases where you're looking to merge multiple contacts into a single field. Please reply to this post with details or schedule time to connect with us directly at: https://go.oncehub.com/OlenRonning

  • A.J.
    A.J. ✭✭✭✭✭

    @Olen Ronning, having the ability to take a column of contacts and have them appear as contacts in a single cell on another sheet would be a huge time-savings for my team. Please add this functionality to the roadmap!

  • MarieM
    MarieM ✭✭

    I would like to use data shuttle to automatically update my contact lists that are exported from a legacy software. Each time the lists are updated, the emails are recognized as text again. I need to go in and manually change the column type to text, save, change it back to contact list, save, and then refresh.

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭

    There are SO many cases in recent years where I've needed to combine people by formula into a contacts column so that a) workflows can be sent to those in that column; and b) Current User can be filtered to enable people in that column to see only what they should be privy to.

    I have struggled with the disappearing multi-contact bubbles over and over again, needing instead to create separate columns for each possible combination of users who should receive workflow messaging or access rows that involve them—and not other rows.

    Another kluge solution has been to create Groups for each combination, but that's very high maintenance and highly error-prone

    Dashboards, Reports, Workflows, Dynamic Views and WorkApps would all benefit from enabling formula-based multi-contact access to specific rows.

    Cases in point:

    • Sheet A contains a changing list of people on a committee, and related Sheet B needs to send alerts under certain conditions to the people on that list. Currently I have to dedicate a column in Sheet B to each possible committee member because, although by formula I can bring them into a single column from Sheet A to Sheet B, Smartsheet fails to recognize the result (past a single Save action) as a set of multi-selected Contacts.
    • Sheet C contains a list of contractors who may be assigned a role related to one or more project rows in Sheet D. I'm unable—without extraordinary gyrations, presently involving manually managing a separate Group for each damn project, and building complex cross-sheet formulas to identify cases where someone has been added to the privy list and needs to be updated to the Group—to ensure that each contractor on Sheet C can see only their rows on Sheet D, and the Gantt timelines for projects they're assigned to in Sheet E.
    • Sheet F contains a list with a single contact per row. I would be able to store the contacts for each entity in Sheet G if I could by formula bring all of them back to Sheet F for workflows on the latter. The only other option is to create a separate full set of contact columns for each additional contact, which is variable per entity, so I can't know whether to create a second AND a third AND a fourth ... set of columns because each entity should ideally be able to designate x contacts. Doing that (especially since we can't group columns like we can rows) would then make a really wide sheet to scroll across (first/last/full/email/phone/street/city/stat/zip = 9 columns for each potential additional contact for each row). All to say, due to this constraint resulting from Smartsheet not recognizing multiple contacts derived from formulas, we have forced these entities to live with designating a single point of contact who must then handle all further distributions within their entity—again, a serious point of failure because we can't count on every designee from over a hundred entities to follow thru on passing along all messages to their colleagues.

    Simply enabling multi-select Contact columns to recognize formula results—exactly as they ALREADY do before pressing Save—would solve all of these cases, and plenty of others.

  • @Olen Ronning I also consider this functionality to be very useful.

    My use case is as follows:

    1. Sheet A contains all active employees of the organization in two companies and several areas in each. Some employees hace managerial roles as well. This is updated by HR when somebody enters or leaves the organization.
    2. Sheet B is a Helper Sheet used to format the information from Sheet C (information on times used on projects by employees). Sheet B has two columns that collects from Sheet A those employees/managers that are enabled to see each particular line of the report.
    3. Dashboard D presents Sheet B using the filter for "CURRENT USER" in the corresponding column.

    As a result the dashboard that contains all employees information, but depending on the current user, the particular employee will only see those lines he is supposed to see (his numbers and his AREA colleagues numbers) but not the information for the rest of the employees.

    Obviously this does not work since those columns defined as "Contact list" does not behave as such when a formula brings a group of names from the employees Sheet A. In my consideration this is a very natural behavior for a data type "if you collect data of type X and put it a a list con items of type X the result should respect type X"

    As a result of this limitation we have lots of extra work maintaining multiple dashboards (one for each area) and limit access by means of distributing the corresponding link only to the area members through a link located in Microsoft Teams in a team whose members are the area employees.

    I hope you reconsider this in your road map soon enough!!

    Best regards,

    Antonio

  • Good to hear. Out of curiosity, how are you then using that cell in the other sheet?

  • A.J.
    A.J. ✭✭✭✭✭

    We have a workflow to send notification of an approval to the contacts listed in a multi-contact cell (hundreds of contacts) which is on another sheet. Currently, we manually enter the contacts from one sheet to another. If there is way to do this easier, please let me know. At Engage 2022 we spoke with multiple SmartSheet staff and this was identified as a gap that could not be solved with formula or workflow.

  • Diem
    Diem ✭✭

    Bumping this topic as we have similar use cases and this should definitely be added to the Smartsheet roadmap!