Announcing Formula Support for Contact Columns

Chris Kaliveas · 04/05/21

Gooday,

I have a question pertaining to this thread and was hoping to find out more regarding this new awesome feature.

Using a vlookup or index/match to auto fill a contact column, are you able to set the formula to not update the contact once another condition is met?

Business Case: Sales Transaction occurs through webform. When row is added to sheet,Assigned Tocolumn is auto-populated via lookup andSale Statuscolumn is set to "In Progress”。交易完成后,然后Sta出售tus is set to "Complete”。The contact in the Assigned To column no longer works with the company and the contact for sales is updated from the former contact to the new contact in the source sheet of the vlookup function.

Expected Results: Rows with a status of "Complete" will not have the Assigned To values updated with the new contact.

Note: The contact source will be current state and not maintain historical assigned contacts. In other words, just one row for each contact, not two (old vs new).

Kind Regards,

Chris

Andrée Starå · 04/05/21

Hi@Chris Kaliveas

I hope you're well and safe!

This might help!

Please have a look at my post below with a method I developed.

More info:

Hi all,<\/p>

I hope you're well and safe!<\/p>

I've developed a solution that lets you store the date or value.<\/strong><\/p>

You'd use the copy-row automation and a VLOOKUP or combination of INDEX\/MATCH to make it work.<\/p>

We'd trigger the copy-row to another sheet and get the created date\/other value and then use the VLOOKUP\/INDEX\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\/value that we can use, it will work.<\/p>

Also, this opens up more options with auto-numbering and similar.<\/p>

Depending on other workflows, you could reuse one sheet for multiple workflows.<\/p>

I hope that helps!<\/p>

Let me know if you have any questions!<\/p>

Stay safe and have a fantastic day!<\/p>

Best,<\/p>

Andrée Starå<\/p>

Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>","bodyRaw":"[{\"insert\":\"Hi all,\\n\\nI hope you're well and safe!\\n\\nI've developed a solution that lets you \"},{\"attributes\":{\"bold\":true},\"insert\":\"store the date or value.\"},{\"insert\":\"\\n\\nYou'd use the copy-row automation and a VLOOKUP or combination of INDEX\\\/MATCH to make it work.\\nWe'd trigger the copy-row to another sheet and get the created date\\\/other value and then use the VLOOKUP\\\/INDEX\\\/MATCH to get it back to the main sheet in another so-called helper column. As long as you have a unique id\\\/value that we can use, it will work.\\n\\nAlso, this opens up more options with auto-numbering and similar.\\nDepending on other workflows, you could reuse one sheet for multiple workflows.\\n\\nI hope that helps!\\n\\nLet me know if you have any questions!\\n\\nStay safe and have a fantastic day!\\n\\nBest,\\n\\nAndrée Starå\\n\\nWorkflow Consultant \\\/ CEO @ \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/workbold.com\\\/\"},\"insert\":\"WORK BOLD\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-05-24T11:36:34+00:00","insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-07-31T14:40:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68589\/lock-or-store-date-value-solution-without-using-zapier","embedType":"quote","name":"Lock or Store Date\/Value Solution without using Zapier"}"> https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå| Workflow Consultant / CEO @WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Matt Stewart · 01/06/22

@Barry<\/a>, @Tim<\/a>,<\/p>\n

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.<\/p>\n

Here is some additional information about the functionality that may be helpful for everyone: <\/strong><\/p>\n

\n
Functions that can return a contact will work in contact columns.

\tSimple equality, e.g., =[Assigned To]1<\/p>\n
- \n
  VLOOKUP, e.g., =VLOOKUP([Assigned To]1,{Departments},2,false)<\/p>\n
  \n
  If contacts are contained in the lookup table (2nd argument), then they can be returned by the function as contacts.<\/p>\n\t\t\t<\/li>\n
  \n
  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.<\/p>\n\t\t\t<\/li>\n\t\t<\/ul><\/li>\n
  \n
  PARENT, e.g., =PARENT()<\/p>\n\t\t<\/li>\n
  \n
  INDEX, e.g., =INDEX({Departments},MATCH([Assigned To]1,{Names},0))<\/p>\n\t\t<\/li>\n
  \n
  IF, e.g., =IF(Urgency1=”High”,[Urgent on call]1,[Assigned To]1)<\/p>\n\t\t<\/li>\n
  \n
  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!)<\/p>\n\t\t<\/li>\n\t<\/ul><\/li>\n
  \n
  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:<\/p>\n
  \n
  =”This is some text”<\/p>\n\t\t<\/li>\n
  \n
  =[reference to cell containing text]<\/p>\n\t\t<\/li>\n
  \n
  =JOIN([range of cells])<\/p>\n\t\t<\/li>\n
  \n
  VLOOKUP function that happens to return free text instead of a contact.<\/p>\n\t\t<\/li>\n
  \n
  =LOWER([Assigned To]1)<\/p>\n
  \n
  Output (just text): cameron doane<\/p>\n\t\t\t<\/li>\n\t\t<\/ul><\/li>\n
  \n
  = [Assigned To]1 + \", \" + [Assigned To]7<\/p>\n
  \n
  Just concatenates the display values of these cells as text<\/p>\n\t\t\t<\/li>\n\t\t<\/ul><\/li>\n
  \n
  Etc.<\/p>\n\t\t<\/li>\n\t<\/ul><\/li>\n
  \n
  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:<\/strong><\/p>\n\t<\/li>\n
  \n
  Multi contact column to ->Multi contact column: Contact appears in the multi contact cell.<\/strong><\/p>\n\t<\/li>\n
  \n
  Single contact column ->multi contact column: Contact appears in the multi contact cell.<\/strong><\/p>\n\t<\/li>\n
  \n
  Single contact column ->single contact column: Contact appears in the single contact cell.<\/strong><\/p>\n\t<\/li>\n
  \n
  Multi contact column ->single contact column: Free text conversion (e.g. comma-separated display values) appears in the cell.<\/strong><\/p>\n\t<\/li>\n
  \n
  Free text in a contact column->Multi contact column or single contact column: The free text appears in the cell.<\/strong><\/p>\n\t<\/li>\n
  \n
  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<\/p>\n\t<\/li>\n<\/ul>
  Known Expected Behavior - <\/p>\n
  \n
  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! <\/p>\n\t<\/li>\n
  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. <\/li>\n<\/ul>","bodyRaw":"
  @Barry, @Tim,<\/p>\n\n
  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.<\/p>\n\n
  Here is some additional information about the functionality that may be helpful for everyone: <\/strong><\/p>\n\n
  \n\t
  \n\t
  Functions that can return a contact will work in contact columns.
  \n\tSimple equality, e.g., =[Assigned To]1<\/p>\n\n\t
  \n\t\t
  \n\t\t
  VLOOKUP, e.g., =VLOOKUP([Assigned To]1,{Departments},2,false)<\/p>\n\n\t\t
  \n\t\t\t
  \n\t\t\t
  If contacts are contained in the lookup table (2nd argument), then they can be returned by the function as contacts.<\/p>\n\t\t\t<\/li>\n\t\t\t
  \n\t\t\t
  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.<\/p>\n\t\t\t<\/li>\n\t\t<\/ul>\n\t\t<\/li>\n\t\t
  \n\t\t
  PARENT, e.g., =PARENT()<\/p>\n\t\t<\/li>\n\t\t
  \n\t\t
  INDEX, e.g., =INDEX({Departments},MATCH([Assigned To]1,{Names},0))<\/p>\n\t\t<\/li>\n\t\t
  \n\t\t
  IF, e.g., =IF(Urgency1=”High”,[Urgent on call]1,[Assigned To]1)<\/p>\n\t\t<\/li>\n\t\t
  \n\t\t
  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!)<\/p>\n\t\t<\/li>\n\t<\/ul>\n\t<\/li>\n\t
  \n\t
  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:<\/p>\n\n\t
  \n\t\t
  \n\t\t
  =”This is some text”<\/p>\n\t\t<\/li>\n\t\t
  \n\t\t
  =[reference to cell containing text]<\/p>\n\t\t<\/li>\n\t\t
  \n\t\t
  =JOIN([range of cells])<\/p>\n\t\t<\/li>\n\t\t
  \n\t\t
  VLOOKUP function that happens to return free text instead of a contact.<\/p>\n\t\t<\/li>\n\t\t
  \n\t\t
  =LOWER([Assigned To]1)<\/p>\n\n\t\t
  \n\t\t\t
  \n\t\t\t
  Output (just text): cameron doane<\/p>\n\t\t\t<\/li>\n\t\t<\/ul>\n\t\t<\/li>\n\t\t
  \n\t\t
  = [Assigned To]1 + \", \" + [Assigned To]7<\/p>\n\n\t\t
  \n\t\t\t
  \n\t\t\t
  Just concatenates the display values of these cells as text<\/p>\n\t\t\t<\/li>\n\t\t<\/ul>\n\t\t<\/li>\n\t\t
  \n\t\t
  Etc.<\/p>\n\t\t<\/li>\n\t<\/ul>\n\t<\/li>\n\t
  \n\t
  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:<\/strong><\/p>\n\t<\/li>\n\t
  \n\t
  Multi contact column to ->Multi contact column: Contact appears in the multi contact cell.<\/strong><\/p>\n\t<\/li>\n\t
  \n\t
  Single contact column ->multi contact column: Contact appears in the multi contact cell.<\/strong><\/p>\n\t<\/li>\n\t
  \n\t
  Single contact column ->single contact column: Contact appears in the single contact cell.<\/strong><\/p>\n\t<\/li>\n\t
  \n\t
  Multi contact column ->single contact column: Free text conversion (e.g. comma-separated display values) appears in the cell.<\/strong><\/p>\n\t<\/li>\n\t
  \n\t
  Free text in a contact column->Multi contact column or single contact column: The free text appears in the cell.<\/strong><\/p>\n\t<\/li>\n\t
  \n\t
  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<\/p>\n\t<\/li>\n<\/ul>\n\n
  Known Expected Behavior - <\/p>\n\n
  \n\t
  \n\t
  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! <\/p>\n\t<\/li>\n\t
  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. <\/li>\n<\/ul>","format":"html","dateInserted":"2019-08-22T11:11:57+00:00","insertUser":{"userID":279,"name":"Kara Lumley","url":"https:\/\/community.smartsheet.com\/profile\/Kara%20Lumley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/95\/4e\/n954e10e9b436e9acd6f195bdf2312f07.jpg","dateLastActive":"2020-11-10T15:44:54+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/200646#Comment_200646","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/200646#Comment_200646

How do i have the resulting destination cell with formula show theFirst Lastname as a contact and not the email as a contact? Currently i can only get the contact to populate as a contact as [email protected]instead of Bill Gates. I understand the email is what connects it as a contact again, and thats fine, but final appearance it should be user name.

Genevieve P. · 01/10/22

Hi@Matt Stewart

This may depend on where you are bringing in the contact information from. Are you referencing a System Generated column (such as Created By or Modified By) in your formula? If so, these columns can only display the email address and the contact will appear as the email in your result, as well.

Matt Stewart · 01/12/22

thanks that resolved it!

A Rose · 06/06/23

Hello,

Is there any formula to create a Contact Name and Contact Email?

If I have a list of names and a list of emails, can I add the contact via formula - and it should appear as a regular contact - and not only the email part of it?

Use Case: If Status = "Current" - Assign this contact column to Rep 1; "Rep 1 <[email protected]>".

The Display of the column should be: "Rep 1" and the email "[email protected]”。

(When you add a contact and then change the column to text - it has the above format, but in a formula - I don't know if it's possible.)

Thank you!

Genevieve P. · 06/07/23

Hi@A Rose

No, there currently isn't a function that can combine an email address and a contact name together to create a Contact type of value in Smartsheet. You can use emails directly in Contact columns being output by a formula.

Here's a Product Idea post that you can vote on to add your feature request:Contact List Components: build a contact via formula

Cheers,

Genevieve

A Rose · 06/07/23

Thank you!

I voted up this feature request.

Guys, let's do it!

Here's a Product Idea post that you can vote on to add the feature:Contact List Components: build a contact via formula

Thank you!

Announcing Formula Support for Contact Columns

Comments

Categories