Contact column created by formula keeps reverting to text

Patsy Peterson
Patsy Peterson ✭✭✭
in Formulas and Functions

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!

· Share on FacebookShare on Twitter

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

    · Share on FacebookShare on Twitter
  • Patsy Peterson
    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).

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

    · Share on FacebookShare on Twitter
  • Dustin King
    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.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

    · Share on FacebookShare on Twitter
  • Patsy Peterson
    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.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    https://community.smartsheet.com/discussion/comment/298649#Comment_298649

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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!