Announcing Formula Support for Contact Columns

Shaine Greenwood
Shaine Greenwood Employee
edited 12/04/19 inGeneral Announcements

Community,

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

Use aVLOOKUPformula to assign work based on data in your sheet, such as department. Or use a combination ofINDEXandMATCHto 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.

«1345

Comments

  • Mike Wilday
    Mike 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

    有一个很好的一天!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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 EXPERT CONSULTANT & PARTNER

    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,

    卡ra

  • 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 Meeks
    Tim 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 Smartsheetyes

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    我有工作!一些工作和计算out the formula but it got it!

  • Gordon
    Gordon ✭✭✭✭✭

    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.)

  • Pradeep
    Pradeep ✭✭

    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,

    卡ra

    Dropdown_in_Form.jpg