Combining/Summing Contacts from Multiple Cells in a Single Cell

Greetings,

In a Contact Column, and in a ancestor/child relationship, I would like for parent rows to summarize/list all persons in the child tasks. Is Smartsheet able to perform this action?

E.g.

If the child records list:

John D
Claire H
Emily G

I want the parent to show:

John D, Claire H, Emily G.

I have tried functions like SUM(CHILDREN()) with no success. I expect that this function only works with numerical values.

Thank you.

Find more posts tagged with

Sheets

Formulas

Project Management

Accepted answers

All comments

L_123

Join(Children(),", ") should do what you want

Michael W.

Image of Result, Where names are added together as plain text.

Thank you for your consideration. This helped but did not achieve the desired result.

JOIN(CHILDREN()) for a contact column simply added together all of the names below as plain text.

If a name appeared 3 times, it appeared in the "joined" cell 3 times. I would like a function that summarizes each name and only shows unique values.
The result only shows in plain text. It does not retain the functionality of the "Contact" column with email addresses (capable of sending automations to, etc.)

L_123

If you want unique values, you can use

=join(distinct(children(

That said this is always going to be text without the functionality of the contact column.

I did attempt to use char(10) which is the separator for multi select dropdowns as the delimiter for the join, but this didn't change anything. Perhaps someone else can provide a solution, but I don't believe that smartsheet is capable at the moment to do what you want.

Andrée Starå

Hi Michael,

Unfortunately, as far as I know, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

D.White

All -

I've found a complicated path to combine contacts from multiple cells into one cell. Unfortunately, this seems to be the only solution available at this time.

If you have multiple columns with different contacts in each column that need to be joined together and stored as a contacts:

Insert a helper column with “Text / Number” column properties next to your contact columns
Right click and “Copy” the first cell that contains the contacts you want to combine.
Right click a blank cell in the “Text / Number” helper column and “Paste Special” “Values” into that column.
This will display named contacts with both the name and the email address in the format: John Smith <jsmith@email.com>
Multiple contacts will be separated by a comma, like: John Smith <jsmith@email.com>, Ron Golden <rGolden@email.com>, tsmith@email.com
Right click and copy the next column of contacts into another blank cell into your helper column.
Once all contacts are pasted into individual cells in your helper column use the JOIN function to create one cell that has all contacts listed and separated by a comma, like: =JOIN([Helper Column]1:[Helper Column]4,", ")
Change the Helper Column properties to “Contact List.” Make sure “Allow multiple contacts per cell” is checked.
You will now have one cell that contains all of the contacts. This cell can be used in a Dynamic View to filter users who may access the view.
Note that SmartSheet restricts contact cells to 20 contacts.
Duplicates will be combined into one contact, but they count against you in the 20 contact list per cell.

This is a very tedious method, but it seems to be the only way to join contacts from multiple cells into one cell and to retain the contact properties.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.