Formula to add multiple names to one cell

Rick Girard

I need to create a lookup to select multiple names from 1 sheet and add those names to 1 cell in another sheet:

Sheet 1: (names in multiple cells)

Name - Type

Rick - A

Jane - B

Bob - A

Sheet 2: (put qualified names in 1 cell)

Lookup names = 'Type A' and copy them into 1 cell.

I know VLOOKUP or an INDES + MATCH can find a name and place it in a cell, but i want to place multiple names in the same cell. can that be done ?

Find more posts tagged with

Accepted answers

Rick Girard

Additional data to clarify my request:

I am looking to create a formula that uses the IMPACTED REGION and IMPACTED FUCTIONAL AREAS to result in a Name to be populated in the IMPACTED & RESPONSIBLE RISK OWNER column. the 1st 2 columns (REGION + AREAS) are multiple selectable entries.

Example:

Regions column: US LATAM EMEA APAJ

Areas column: HR - Marketing - Operations - etc...

Owners column: Name 1, 2, 3 etc

example:

Region = US

Area = HR & Operations

Name = Rick (desired name to populate the Owner cell) etc..

(if the IMPACT Region listed US and IMPACT Area listed either HR or Operations, then Owner Name 'Rick' would populate the Owner cell)

What is the proper formula string to create an IF AND OR selection to then select a Name for the Owner?

*I have run into issues where only the top name in the menu only gets selected (ie. if Rick were 1st on top with Jane and Joe following as 2nd and 3rd, those names don't get picked up in a formula string).

thanks for your assistance!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326819#Comment_326819

Since you're using multi-select cells, using the HAS function is key. HAS will treat each standalone value inside a multi-select cell as a distinct value. For example, if I wrote this IF on Row 1 of your screenshot, it would equal "Failure":

=IF([::IMPACTED REGIONS(S)]@row = "US", "Success", "Failure")

But if I use HAS for my logical statement, I would get "Success":

=IF(HAS([::IMPACTED REGIONS(S)]@row, "US"), "Success", "Failure")

Using this, let's get your IF/AND/OR/HAS syntax right:

=IF(AND(OR(HAS([::IMPACTED REGION(S)]@row, "US"), HAS([::IMPACTED REGION(S)]@row, "CAD")), HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, "Insurance")), "NoName", "")

Just to help you see exactly how the pieces come together, we're placing an OR inside the AND, along with the other criteria. The yellow is the AND statement, with the yellow comma and yellow parentheses at the end showing you the parts of the AND. The blue is the embedded OR, the green is the rest of the criteria for the AND, though this could be another OR as well. Taken together, the entire AND and its parts make up the logical statement portion of the IF:

The result of this formula for Row 2 would be "NoName".

Here is the above in use with two OR statements embedded in the AND, from my test sheet. The data matches one of the two options in each OR statement.

Change one of the multi-select cells to remove all of the criteria that would match, then I get "Fail":

Rick Girard

Thanks Jeff and Andrea, yes i was thinking along those lines as well, some column names are long and the menu options are as well, will be working on reducing those to see if i can make it fit. I may also look to offload the match outputs to another column and x-ref them but it may be a wash in terms of char count. thanks again !

All comments

ker9

Thank you to @Rick Girard , @Jeff Reisman for this post.

I used it to help me join a collection of names. Unfortunately, it doesn't want to recognize those names in a Contact Column that allows multiple names.

The weird thing I found... I changed the delimiter from ", " to Char(10) (no quotes) and it showed the names as contacts with the little badge, BUT when I saved the sheet, it converted them back to names and not contacts.

After saving, if I reset the column to text and then back to contact, it shows the names as contacts. Save the sheet and it converts them back to names. So close and yet so far. If you have found a way around this, would love to hear it.

Jeff Reisman

@ker9 Unfortunately, Contact columns in Smartsheet are pretty complex.

The only way I've found to be able to work with them successfully in formulas is one-way. In other words, if I have an email address in a Text/Number "Email" column, I can use a formula in a Contact column like =Email@row to create a valid Contact value out of the text email address. I'm doing that all over the place in a current project, where we need external users to enter email addresses on form submissions, and then we use those addresses later in automations.

Now, if you haven't set up a real Contact record with first name/last name/ email address for an email address, you can match against them using text fields or whatever. For example, if my multi-select Contact column contains "Test@test.com", I can do something like =IF(HAS(Contact@row, "Test@test.com"), "Yes", "No") and will return a Yes value.

ker9

@Jeff Reisman - thank you. Was hoping to use this in a "Current User" scenario for a report. It does work with multiselect contact columns, but not the one that has my join/collect formula, which probably means I need multiple reports. :(

ker9

@Jeff Reisman - I learned from another thread that I should not use Char(10) but that the delimiter ", " is appropriate for this column type (even though it doesn't work). (Courtesy of @Paul Newcome in this thread.)

I did try DataMesh and found that you can DataMesh the formula to a contact cell (as data, no longer a formula) BUT it won't work immediately. You have to change it to text and back to contact, but then it sticks (until you DataMesh again). Still not a perfect solution but if the data doesn't change much, it can work.

Jeff Reisman

@ker9 Do you have any experience using the Smartsheet API? I just remembered I worked on something like a few weeks ago, trying to populate a multi-picklist column in a sheet with values from an Excel file. So just now I did some digging on how I could adapt this for multi-contact columns. It's not easy, but I just got it to work! The most I had done before about two weeks ago was read other people's code, never wrote any of my own, so I've learned a lot. So if you have API access and are a little familiar with API calls, or just want to journey down the code rabbit hole, follow along below.

My process above is being done in Alteryx, which at like $5k per seat is not cheap. However, you can use Postman, which is free! If this is a regular process you'll have to do, I'm sure there's a way you can script this out in Postman.

The API Documentation is here: https://smartsheet.redoc.ly/

First thing is to get the column IDs from the sheet, using something like this:

https://api.smartsheet.com/2.0/sheets/insert_SheetID_here?accessApiLevel=1

Once you know the column IDs you want to populate, you'll want to construct a JSON string to use in a POST or PUT command. (POST is for writing a new row, PUT is for updating an existing row.) The multi-contact column's portion uses this syntax:

{"columnId": your multi-contact column's ID goes here, "objectValue": {"objectType": "MULTI_CONTACT", "values": [ {"objectType": "CONTACT", "email": "TestUser1@fakedomain.com" }, {"objectType": "CONTACT", "email": "TestUser2@fakedomain.com" } ]}}

Essentially, each contact in your list has their own little section within the MULTI_CONTACT data, consisting of an "objectType" value and an "email" value.

So put all together, the JSON looks something like this:

[{"cells": [ {"columnId": 164400025185444, "value": "This is my Smartsheet code"} ,{"columnId": 44598856859004, "value": "Interesting information"}, {"columnId": your multi-contact column's ID goes here, "objectValue": {"objectType": "MULTI_CONTACT", "values": [ {"objectType": "CONTACT", "email": "TestUser1@fakedomain.com" }, {"objectType": "CONTACT", "email": "TestUser2@fakedomain.com" } ]}}, {"columnId": 2487537454584660, "value": "Did you vote this week"} ] }]

Have fun!

ker9

@Jeff Reisman - Thank you, this is awesome! I played around a tiny bit with APIs and really want to find time to work on it more - this might be my opportunity. Thank you very much!

Jeff Reisman

@ker9 My pleasure. I had been a little peeved at the difficulty of working with these multi type cells, so this was a great chance to get it worked out.

One more thing: you can set the displayValue for the contact by adding the "name" parameter for each contact:

{"objectType": "CONTACT", "name": "Test User", "email": "TestUser1@fakedomain.com" }

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.