Announcing Formula Support for Contact Columns
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.
Comments
-
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
-
Hi Shaine,
Wow! Thanks!
Have a fantastic day!
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Art,
I just sent you an email - looking forward to solving this!
Best,
Kara
-
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
-
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!
-
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
-
i got it to work! Took a bit of work and figuring out the formula but it got it!
-
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)
-
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
