20

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

In reply to by Mike Wilday

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.

Hi Shaine,

Wow! Thanks!

wow

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

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.

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

SEAN WILLIAMS

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

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!

In reply to by Gordon

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.

 

 

In reply to by Pradeep

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

In reply to by Kara Lumley

Although I appreciate the new feature, I can't help but think there should be a better way to get contacts auto-populated into a contact list column from a form input, if you have the form restricted to account users only.

As a workaround, now that we can have formulas, I created a separate Smartsheet user list sheet with all our Smartsheet users email addresses in one column and their "contact" in the 2nd column.  That sheet is then referenced in my original sheet's contact list column VLOOKUP formula, which searches based on the email address returned in the system-column "created by".  Very clunky workaround, and reliant upon ensuring the referenced user list sheet is kept updated as we add new users to our Smartsheet account.

In reply to by Gordon

Gordan,

I agree, the way contacts are handled are not very easily understood, as there are 3 "types". Global contacts, which are include plan members on multi-user plans as well as any uploaded by sys admins, personal contacts, added or uploaded via your personal settings, and contacts added on sheets that are neither of those, but added when a sheet is shared to a collaborator, and can be specific to sheets.

In reply to by Pradeep

My VLOOKUP formula in the contact list column appears to be replicating properly into new rows added when a form is completed, however a simple multiplication formula in another column is not.

Shaine this is a great new feature to Smartsheet- THANK YOU!  - it seems like a minor change but I believe its impact will be significant especially in automation workflows.

I have already been deploying it and it works except it seems to only work with a formula that returns the email address. If the formula returns the contact name in the exact same formula it does not recognize the name as a contact. If I hover the mouse over the name returned by the formula smartsheet offers to add it as a new contact.

It seems odd because my formula is a simple "if" statement - here is the two if statements - the first one with emails is recognized as a contact and the other is not:

=IF(Region58 = "AMER", "[email protected]", IF(Region58 = "AUS", "[email protected]", "[email protected]"))

=IF(Region59 = "AMER", "Brian Morton", IF(Region59 = "AUS", "Gary Wilson", "Juan Garza"))

The names are a perfect match for what is showing in the name in "my smartsheet contacts". I changed the email address for privacy reasons.

The user experience and reports would be much better with names displaying. Is there a way to have it work with names and not just email addresses?