Announcing Formula Support for Contact Columns

Shaine GreenwoodShaine Greenwood Employee
edited 12/04/19 in Announcements
08/13/19 Edited 12/04/19

Community,

We are pleased to announce that one of our most requested enhancements — using formulas in contact columns — is now available.

Use a VLOOKUP formula to assign work based on data in your sheet, such as department. Or use a combination of INDEX and MATCH to dynamically assign work based on changes in your sheet, such as a status field change.

Once assigned, automated workflows can notify the assignee of the new work item or request an update. (More on automation.)

Now it's much faster to get the ball rolling on work items with no manual intervention needed.

Previous134

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭

    WooHoo. You better update this article. It still says it can't be used in contact columns. https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet#noformula

    dance-minion.gif

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Shaine,

    Wow! Thanks!

    wow

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi Mike,

    I updated that article before I made the announcement.

    It specifically says that formulas can't be placed in Contact list fields being used for resource management — which is true.

    If you're not using resource management in a sheet, you can place formulas in Contact list fields.

  • So, if I have a column with the Contact Name, and I have the e-mail address (via Index/Match), how does it become a Contact in the Contact column? It just puts the information in as text, not as an actual contact.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Is it an email address that is added from a formula in the contact column?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi Art,

    I just sent you an email - looking forward to solving this!

    Best,

    Kara

     

  • Ratka ZdenekRatka Zdenek ✭✭✭✭✭

    It Works. If you are looking in the external table by VLOOKUP function and search value is Contact type, then the result is again the Contact type value. I tried that with multiple contacts in one cell and there is no issue as well.

    Best

    Zed

  • Tim MeeksTim Meeks ✭✭✭✭✭

    Very timely release!  I've got an application that needs this along with Dynamic View that we just purchased. Will be working on this the rest of this week!yes

  • Great work everyone - love the on-going release of new features & functionality.

     
  • So happy to see this enhancement finally become a reality. I think it may have been my most requested update. Works perfectly too. Thanks Smartsheet yes

  • Tim MeeksTim Meeks ✭✭✭✭✭

    i got it to work!  Took a bit of work and figuring out the formula but it got it!

  • GordonGordon ✭✭✭✭✭

    Testing this out and I'm running into an #UNPARSEABLE error when using the VLOOKUP to return an employee name from a separate table that has email addresses and contact lists.

    Returning to my earlier post, we have a request form setup that also has the system generated column for the user completing the form.  That appears to return the email address for the user int he system column.  What I'd like to do is setup the contact list column in the sheet to lookup the employee from the email address that is returned in the system column.  I already have a separate table that has the email addresses and names in the 2nd column of the table.  The 2nd column is also a contact list column.

    Here's my formula in the Contact List column:

    =VLOOKUP([Created By]1709,{Email Address Lookup},2,)

    [Created By] is the system-generated column that returns the user's email address.

    {Email Address Lookup} is the external sheet that I referenced the first two columns in, with the 2nd column being the contact list column in that sheet that has the employee's name.

    Help!

  • Hi Gordon,

    Looks like you have an extraneous comma in your formula. Try this:

    =VLOOKUP([Created By]1709,{Email Address Lookup},2)

    Otherwise, you might want to consider including the FALSE parameter so VLOOKUP will find an exact match. For example: 

    =VLOOKUP([Created By]1709,{Email Address Lookup},2,false)

    (More on VLOOKUP.)

  • Hi Shaine,

    I could add Vlookup formula to contact list, but when a new row is added through form to smart sheet the cell with contact list doesn't Auto fill

    this auto fill is required to request approval.

     

     

  • Hi Pradeep,

    In order for the list of contacts to appear in the form dropdown, you'll want to prepopulate the list of contacts in the column properties, as in the screenshot.

    If you do not pre-populate, the form user will need to type in the email address, and that will be a contact that the VLOOKUP will work with. If this is the case, I advise to use the Help text in the form to indicate this. 

    Hope this helps,

    Kara

    Dropdown_in_Form.jpg

Sign In or Register to comment.