Announcing Formula Support for Contact Columns

Shaine Greenwood

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.

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

Andrée Starå

Hi Shaine,

Wow! Thanks!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Shaine Greenwood

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.

Art Schneiderheinze

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å

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

Kara Lumley

Hi Art,

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

Best,

Kara

 

Ratka 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 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!

sean59916

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

 

Karen B

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 

Tim Meeks

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

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!

Shaine Greenwood

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

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.

 

 

Kara Lumley

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