Contact column created by formula keeps reverting to text

Hi Smartsheet Community.

I'm hoping that this is a setting or something simple I'm missing. I have an employee list of 1500+ employees that is being auto-updated by our IT team once a week. Currently the weekly info dump puts in the Name and Email address (along with a lot of other information that is useful, but not relevant here). I use this in a variety of VLOOKUPs.

I have set up the proper formula to combine Name with the email address (=Name@row + " <" + [Email Address]@row + ">").

So, here is my issue:

Every time I (or anyone with editor access) opens the sheet, the contact cells revert to the text version of the formula results (First Surname <emailaddress@orgname.com>). After every update, I have to go into the sheet and convert the column to text, and then back to Contact.

Is there any way to make the Contact format permanent, AND automatically apply to new employee entries?

Any assistance is greatly appreciated!

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Patsy Peterson

Contact Columns are little bit tricky when it comes to formulas. They use the email address to recognize a value as a contact, so I would recommend simply having =[Email Address]@row as your column formula.

This will set it to be a contact and you won't need to change the column type each time you access the sheet (although you may need to Save the sheet for it to recognize the contact). This column can then be used for automations or other alerts. However the formula won't show the associated First and Last name for that email's account unless you hover over the cell. Will this work for you?

I will note that your current formula forces text on top of the Contact instead of pulling the actual contact name. In the image below, I've used your formula in "Contact" and the formula that just pulls the Email in the "Other Contact" column.

Screen Shot 2021-05-21 at 3.12.19 PM.png

Let me know if =[Email Address]@row will work for your purposes or not.

Cheers,

Genevieve

Genevieve P.

Hi Patsy,

That makes sense - to have two lookups (one for the email to set your workflow to send to, and one for the name for the email). Let me know if you're seeing any other weird behaviour!

Just a note, if you have a lot of VLOOKUP formulas, you may want to switch over to an INDEX(MATCH type of formula instead. (Paul has an example of this formula structure, here).

INDEX(MATCH only needs to two columns (the one to pull and the one to match) so it's less of a strain than a large table referenced in a VLOOKUP. If the columns are next to each other then it may not make a difference, but if you're referencing a large part of the grid in your range then I would suggest changing up the formula.

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Patsy Peterson

Let me know if =[Email Address]@row will work for your purposes or not.

Cheers,

Genevieve

Patsy Peterson

Hi Genevieve.

Thank you! This doesn't quite do what I want it to, but it is a solid work around. If I craft my VLOOKUPs to also grab the text name, I can use that to address the recipient of the automation by name instead of the contact field.

I'll have to see how the data dump impacts the email in the contact column (I assume it saves automatically, if not, I'll still need to go in and do something in the sheet).

Genevieve P.

Hi Patsy,

That makes sense - to have two lookups (one for the email to set your workflow to send to, and one for the name for the email). Let me know if you're seeing any other weird behaviour!

Just a note, if you have a lot of VLOOKUP formulas, you may want to switch over to an INDEX(MATCH type of formula instead. (Paul has an example of this formula structure, here).

Cheers,

Genevieve

Dustin King

Guessing there is not a setting then? I have the same issue, only mine is pulling multiple email addresses for the contact row.

=JOIN(COLLECT({ALM EMAIL ID}, {ALM manager}, [Manger to Notify]@row), ", ")

Trying to bring multiple Emails into one contact row for a dashboard filter. The data changes daily so I need the formula to look up the proper team members for each row.

Genevieve P.

Hi @Dustin King

The formula you have would bring many text values into one cell, either displaying the names of the contacts or the email addresses (depending on how your "ALM EMAIL ID" column is formatted).

At this time there currently isn't a way to populate multiple Contact type of values into a Contact column using a formula. Please let the Product team know of your request, detailing your use-case, by filling out this form.

Thanks!

Genevieve

Patsy Peterson

Hi @Genevieve P.

Just for the record, I have now learned the INDEX/MATCH formulas, and they make my life so much easier! Thank you so much.

Genevieve P.

https://community.smartsheet.com/discussion/comment/298649#Comment_298649

Oh wonderful! That's good to hear. Thanks for following up!

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.