Announcing Formula Support for Contact Columns

Gordon

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.

Gordon

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.

Kara Lumley

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.

Kara Lumley

This article might be helpful - https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Barry Bowles

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", "BMorton@abccompany.com", IF(Region58 = "AUS", "GWilson@abccompany.com", "JGarza@abccompany.com"))

=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?

Kara Lumley

Hi Barry,

Thanks for the note - there was a flaw in the logic we found and it is being corrected with our next release - mid next week is the plan.

Best,

Kara

Tim Meeks

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!

Barry Bowles

Thanks Kara I look forward to the update

Barry

stephanie.greenspan100556

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 'Jane.Doe@1234.com'.

 

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 'Jane.Doe@1234.com'.

 

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

 

Kara Lumley

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

Andrée Starå

Hi Kara,

Thanks for sharing this excellent information!

And.

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

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Barry Bowles

Thanks very much for the update Kara - I shall re-test it in my worksheet and look forward to making this work.

 

regards

Barry

Kara Lumley

Hi Stephanie,

I just sent you an email, happy to help.

Best,

Kara

Mike Wilday

We'd love to hear if and how you figure this out. Please share any resolutions with the community!  

Rob Eberstein

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.