49

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.

Andree_Stara

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?

I am using another source sheet to store the emails and noticed it pulled emails addresses when a text drop column but does pull the Contact Name when I changed to the column to a Contact List.

@Barry.. thanks for taking the time to research!

In reply to by Tim Meeks

@Barry, @Tim,

The correction to how we handled first/last names and emails vs just emails was pushed out last night. Let me know if you see anything unexpected.

Here is some additional information about the functionality that may be helpful for everyone: 

  • Functions that can return a contact will work in contact columns.

    Simple equality, e.g., =[Assigned To]1

    • VLOOKUP, e.g., =VLOOKUP([Assigned To]1,{Departments},2,false)

      • If contacts are contained in the lookup table (2nd argument), then they can be returned by the function as contacts.

      • Note that the first argument could also reference a contact. Or, it could reference a string that happens to match the display text of a contact.

    • PARENT, e.g., =PARENT()

    • INDEX, e.g., =INDEX({Departments},MATCH([Assigned To]1,{Names},0))

    • IF, e.g., =IF(Urgency1=”High”,[Urgent on call]1,[Assigned To]1)

    • The above functions will work based on contact input data that already exists in other cells. If existing contact(s) are used as input to the functions, then they can now be successfully returned as contacts. There is not a new function to create contacts from a formula (yet!)

  • Formulas that output text, result in free text in the contact cell, just like writing these formulas in a text/number column today. There are many examples, including:

    • =”This is some text”

    • =[reference to cell containing text]

    • =JOIN([range of cells])

    • VLOOKUP function that happens to return free text instead of a contact.

    • =LOWER([Assigned To]1)

      • Output (just text): cameron doane

    • = [Assigned To]1 + ", " + [Assigned To]7

      • Just concatenates the display values of these cells as text

    • Etc.

  • The above functions work based on contact input data that already exists in other cells. If existing contact(s) are used as input to the functions, then they can now be successfully returned as contacts. These formulas should work in the following combinations of single-contact/multi-contact input and output columns. In particular:

  • Multi contact column to ->Multi contact column: Contact appears in the multi contact cell.

  • Single contact column ->multi contact column: Contact appears in the multi contact cell.

  • Single contact column ->single contact column: Contact appears in the single contact cell.

  • Multi contact column ->single contact column: Free text conversion (e.g. comma-separated display values) appears in the cell.

  • Free text in a contact column->Multi contact column or single contact column: The free text appears in the cell.

  • Formulas that output data types other than contacts or text (e.g. dates, numbers, booleans, etc.) are not supported and will return an error code: #CONTACT EXPECTED. This is similar to how date columns work today; formulas calculate to #DATE EXPECTED if they output anything other that dates or text. Checkbox columns have a similar error, #BOOLEAN EXPECTED

Known Expected Behavior - 

  • A contact column that is mapped to Resource Management will not accept formulas. If a formula is entered in that column, the formula is placed into the “name field” of a contact, and is escaped (has an apostrophe in front of it).  We’re working on allowing both to work together! 

  • If formulas are in a contact column, then Resource Management is enabled for that column, the value (result) of the formula is retained, but the actual formula is removed. 
Andree_Stara

In reply to by Kara Lumley

Hi Kara,

Thanks for sharing this excellent information!

And.

There is not a new function to create contacts from a formula (yet!)

wow

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Hello,


EDIT:  To back up a minute, I think the bigger issue is that the vlookup when to a contacts list - even when the names have been added as contacts - is still not autofilling new rows with the information.  When I type in the team name Assembly, it is still not pulling that contact name automatically with the VLOOKUP auto-fill.

-----------------------------------


I find that I cannot chain-Vlookups.  I used the Vlookup to pull the manager's name based on a team name (not a contact drop down list):

i.e. 'Assembly Team' pulled in 'Jane Doe'. 

 

But then I wanted the name 'Jane Doe' to chain-pull in a Contact email address.

i.e. the VLOOKUP pulling in 'Jane Doe' would then trigger a second VLOOKUP for a contact column to pull in '[email protected]'.

 

This way, all I have to type in is the team name, and both the name and the email address will pull into two separate columns.

i.e. column 1 type in 'Assembly Team', triggering column 2 to populate 'Jane Doe' triggering column 3 to populate '[email protected]'.

 

However, chaining this second VLOOKUP will not work.  Is this a functionality that can be added, or does it already exist and I am doing it wrong?

Thank you,

Stephanie

 

Rob Eberstein Cheetah Transformation Smartsheet Consultant

Thanks for the work in making formulas work in Contact columns. This is great functionality. We experienced some problems with this earlier today. Names were coming through via a VLOOKUP formula from a Contacts sheet, however, we were only seeing text. When I clicked on a name, I could update to pick up the contact, but this lost the link. 

However, looking at the sheet this afternoon, the contacts themselves are coming through perfectly, so I imagine any updates in the background have now come through. Working perfectly.

 

 

This is great news however I am having a few problems getting it to work seamlessly.

I am using an Index Match formula to bring in their email address from a Contact List field from another Smartsheet based on a common key/reference they both share.

The problems I am having are two fold:

1. There is a delay of several minutes before the formula takes effect which is off-putting for users leaves me with doubts about the next stage of my automation efforts (I'm going to be using the Contact List bought in to trigger alerts and notifications so it needs to be reliable). Attached shows that I still receive the error message that you got when previously attempting formulas in Contact Lists.

2. When a name has been added alongside the email address to the Contact on the Smartsheet I am bringing the data in from, in some instances, only the name was bought across and not the email address. I had to copy the contact list information across to a spreadsheet and manually convert all the contact names back to email addresses and then copy and paste this back into the source Smartsheet to sort this which isn't ideal.

 

Are these issues unique to me or is this a recognised issue that's being worked on in the background?

Rob Eberstein Cheetah Transformation Smartsheet Consultant

In reply to by neil.pattinson…

Hi Neil,

There is some current instability. My sheets have been pulling in the correct Contact for the last 24 hours, but suddenly I also experienced the #CONTACT COLUMN DOES NOT SUPPORT message. After a while, it came back, so I suspect something is updating somewhere on the Smartsheet servers. Fingers crossed it comes back for you as well.

In reply to by Rob Eberstein

Same issue occurring with all our contact columns containing formulas -- working fine, then refreshes and error, then refreshes and works.  When first opening the sheet there is an error but after a refresh the values come thru -- tried all suggested fixes.  This is also causing our reports to not pull correctly.

Hi Neil and Rob,

Thanks for letting us know. If you see that #CONTACTCOLUMN error again, please refresh your browser and it should go away. We found the code that is causing that this week and working on a fix for it.

When the contacts are not coming over via a formula as expected, here are a few things to check:

Both contact columns are the same type: either both should be multi-assign or single assign. If there's a multi-assign populating a single assign column, the contacts will be converted to text. 

If the contact column was converted from a dropdown list - go to the column properties, select dropdown list, remove the entries (emails and/or First/Last names, click on Contact List again then save (don't save prior to selecting Contact List again :) )

Let me know if these do not help with that!

Best,

Kara

In reply to by Kara Lumley

Thanks Kara.

You're right about the multiple contacts checkbox. Looks like I didn't tick this when I converted the existing column with a formula in to a contact list, hence why they didn't convert cleanly.

Good to hear that the issue has been found, it might also be worth checking that the fix put in place also corrects this message coming across (Despite it not showing in the Smartsheet) to the spreadsheet when it is exported.

I am still having the issues with some of my cells -- yet not all and they all have the same formula running: =INDEX({Tenant Coordinator}, MATCH([Yardi Code]@row, {Yardi Code}, 0))

This is the same formula that is running in the column to the left -- just with a different Index Column -- match is identical. =INDEX({Ownership}, MATCH([Yardi Code]@row, {Yardi Code}, 0))  And all values are pulling correctly which leads me to believe there is still an issue with formulas in the Contact Column.

 

Never mind -- I ended up finding out the source sheet WAS originally a drop-down list -- I did Kara's suggestion on clearing out the list then changing it back to a contact column and it seems to have fixed the issue.

New Update -- after clearing out per Kara's suggestion -- every 5 minutes or so I am receiving an error (again) that contact list doesn't support formulas -- using a simple =Parent() on the source sheet...these are the only cells affected.

Hi Kristen,

Just sent you an email, will try to help.

Thanks,

Kara

I'm having issues with VLOOKUP. First it seems to work, but as soon as one of the referenced values is changed, the formula generates #CONTACT COLUMN DOES NOT SUPPORT message. However once the sheet is refreshed the error goes away, but the sheet is asking to save it. If not saved, then the error message stays, if saved then it goes. Not sure how to deal with it as I cannot re-save the sheet each time when any of the referenced values are changing..

Any suggestions are appreciated.

Thanks,

Maxim

In reply to by Maxim Lobko

I'm seeing the same thing that Maxim describes except for the error message. If I update the source sheet that  the VLOOKUP Is pointing to, the vlookup column appears as in the attached screen shot. 

The main issue is that it affects the Dynamic View that is using this vlookup to filter. The user was seeing items but is no longer. 

To resolve, I then have to resetup the vlookup formula and copy it down. Then it works. 

 

Tim

Maxim, Tim,

We are so sorry that you are seeing the formula errors. We are aware and have a correction being pushed out next week. I will update this channel as soon as that is live.

Thank you,

Kara

In reply to by Maxim Lobko

Hi Maxim,

Yes, I'm so sorry you are still seeing this. We had hoped to have the fix out last week, and it has now shifted to this week. I will update the thread once the fix is available.

Thank you,

Kara

In reply to by Maxim Lobko

Maxim (and any others experiencing error messages with formulas in contact columns):

Please log out of Smartsheet and log back in. All of the #nomatch and #contactcolumnsdonotsupportformula errors should be resolved. Please let me know if not.

Thank you,

Kara

I am using INDEX / MATCH to populate a cells in a CONTACT LIST column from a CONTACT LIST column in another sheet. However it is returning the individuals first and last name as TEXT and not making it a Contact. 

How can I make this return a contact?

Reading the posts it seem like I need to create a Contact List sheet and then vlookup the email address. 

Or am I missing something?