Use Contact List (formatted column) as data source to lookup info

Black_Dog

I have spent several hours searching for an answer to what I think should be something simple to do. I have a sheet with information related to a contact, (address, supervisor, ph numbers etc). All I want to do is reference the name in the Contact List (formatted column) to pull up other information related to that person. I have tried a number of things but I always get an error of some type. Ultimately I'd like to use the retrieved data to do things like use may soft phone to dial from smart sheet or display data to about the site that person works at etc.

Is this even possible if so how?

jmyzk_cloudsmart_jp

Hi @Black_Dog

Yes, that is simple.

You only have to use formulas like INDEX(MATCH(), INDEX(COLLECT()), or JOIN(COLLECT()).

If the data you are retrieving are text data, I prefer the JOIN(COLLECT()) combination, as you do not handle the errors in case there is no match for the input name.

For example, the formula for the department in the following demo Employee Data is;

• =JOIN(COLLECT(Department:Department, [Full Name]:[Full Name], [Full Name]#))
• meaning: Collect the data in the Department range with the Full Name range data is [Full Name]#, and join them all. Note, [Full Name]# is how to reference field data in a Sheet Summary.

https://app.smartsheet.com/b/publish?EQBCT=d4f1b00bc7d444fa8d2878422db1a967

softphone

Referencing data in a softphone or smartphone is much simpler.

First, input part of the name, then candidates will pop up.

Then, choose the one you want. (Nolan Brooks, for example.)

Expand by clicking the downward to show the row data.

I found that Smartsheet has improved its mobile app quite a lot.😄

Black_Dog

Thanks for your answers, but they are not what I'm looking for.

I have no issues with combining text cells and what not. My problem is when the I'm referencing a column that have been formatted as a Contact List. When you hover over the name you get the "pop up" box with the persons email, phone number and a few other bits of information depending on what was entered/allowed. It is the name in the Contact List formatted column that I want to reference to pull up data from another sheet. The Contact List formatted column does not allow you to dial or email from the pop up. The information you can put into the "Contact List" is limited.

I created a sheet with a bunch of information I got from HR that includes job titles, building address, functional groups and business units etc.. To access this info I would normally just match it up to the HR info by using the first and last names. However, since names in the Contact List formatted cells are not Text I'm unable to reference them to them to pull the needed info from the other sheet.

I guess another way to but it might be, how do you convert an entry in a Contact List format to text so vlookup, index match or some other method can be used to get the related information that can't be held in a Contact List formatted column. Secondly, how do you do it if there are multiple names entries ?

jmyzk_cloudsmart_jp

Hi @Black_Dog

The profile information, as shown below, is stored in the User management of the Admin Center. Currently, Smartsheet does not offer bulk export of the profile data.

Your sys admin has typically imported these data from your company's HR data. So, if you can access similar data, you can INDEX(MATCH or VLOOKUP using email or name as a key.

The information can be accessed programmatically using API.

So, if you can use API, get the list of users. Then, from the list, get the user ID.

Finally, with the user ID, get the user's profile data, such as mobile phone number.

Example of user profile information you will get using API.

https://smartsheet.redoc.ly/tag/users#operation/get-user

{

 "id": 48569348493401200,

 "account": {

  "id": 122454719915908,

  "name": "Smartsheet Org"

 },

 "admin": true,

 "alternateEmails": {

  "id": 8150532427671428,

  "confirmed": true,

  "email": "johnathan.doe@smartsheet.com"

 },

 "company": "Smartsheet",

 "customWelcomeScreenViewed": "2020-08-25T12:15:47Z",

 "department": "Engineering",

 "email": "john.doe@smartsheet.com",

 "firstName": "John",

 "groupAdmin": true,

 "jiraAdmin": true,

 "lastLogin": "2020-10-31T12:15:47Z",

 "lastName": "Doe",

 "licensedSheetCreator": true,

 "locale": "en_US",

 "mobilePhone": "555-867-5309",

 "profileImage": {

  "imageId": "u!1!nAtdn5RJB_o!k6_e_3h2R3w!wmYXPek-yVD",

  "height": 1050,

  "width": 1050

 },

 "resourceViewer": true,

 "role": "Software Developer",

 "salesforceAdmin": false,

 "salesforceUser": false,

 "sheetCount": 42,

 "timeZone": "US/Pacific",

 "title": "Senior Software Engineer",

 "workPhone": "844-324-2360"

}

Black_Dog

Thanks I'll look into your suggestion.

I don't need to do a bulk download or upload. I'm not sure the API is the right way to go either, as I need to use the name from (contact list formatted cell in a project) to do the lookup of information that is not stored , so I would need to do a "get" on that cell. Simply being able to read the cell and convert it to text, so it and serve as a source for vlookup would work.

jmyzk_cloudsmart_jp

@Black_Dog

In that case, you can reference the contact list column in a text/number column.

=[Multiple Contacts]@row

or

=[Contact List]@row

In the Multiple Contact case, use the TEXT function like FIND(",", [MMultiple Contacts]@row) and LEFT, MID functions to get individual contact.