Current user + formula in Contact List?

nerdesigner · 02/26/20

I'm trying to auto-assign an owner to a task based on the task type:

Screen Shot 2020-02-26 at 3.30.48 PM.png

The Owner column is a Contact List Column and the email addresses specified in the formula are associated with current user accounts (one being myself).

But when I use a Current User filter, nothing shows!

Screen Shot 2020-02-26 at 3.29.14 PM.png

Screen Shot 2020-02-26 at 3.29.31 PM.png

I can only assume that using a formula to fill a contact list isn't compatible with the Current User value?

I have tried using First Last name, and I've also tried the format straight from a Contact cell with no luck--that would be the full:

First Last

<email>

And that did not work either.

Thoughts? Is this simply not an option? I'd love to be proved wrong!

Thanks fellow Smartsheet-ers!

Genevieve P. · 02/27/20

Hi @nerdesigner

If your Owner column is a Contact List type of column and those emails are contacts, it should work. That said, sometimes when formulas are calculating (like when the sheet is refreshed) it might just bring in the email as text until the next Save when it can recognize that text as a Contact.

Instead of typing out the two email addresses within the formula, I would suggest creating two Contact List type fields in your Sheet Summary window for your sheet.

Then in your formula you can reference the actual Contact (versus a typed out email). This will ensure that the value returned is always a Contact type of value, which can then be used in a "Current User" filter.

Screen Shot 2020-02-27 at 1.13.13 PM.png

Your Sheet Summary fields can be found from the sheet icon on the right menu. When you click the "+ New Field" button, just make sure you add a Contact List type of field.

To reference a Sheet Summary field in a formula, it's the name that you gave the field (in my case, Developer and Designer), then with a # symbol:

=IF(CONTAINS("Design", [Request Type]@row), Designer#, Developer#)

I also used @row instead of the exact row number. Here are some Help Center articles you may want to review:

Let me know if I can clarify anything further!

Cheers,

Genevieve

Genevieve P. · 02/27/20

Hi @nerdesigner

If your Owner column is a Contact List type of column and those emails are contacts, it should work. That said, sometimes when formulas are calculating (like when the sheet is refreshed) it might just bring in the email as text until the next Save when it can recognize that text as a Contact.

Instead of typing out the two email addresses within the formula, I would suggest creating two Contact List type fields in your Sheet Summary window for your sheet.

Then in your formula you can reference the actual Contact (versus a typed out email). This will ensure that the value returned is always a Contact type of value, which can then be used in a "Current User" filter.

Your Sheet Summary fields can be found from the sheet icon on the right menu. When you click the "+ New Field" button, just make sure you add a Contact List type of field.

To reference a Sheet Summary field in a formula, it's the name that you gave the field (in my case, Developer and Designer), then with a # symbol:

=IF(CONTAINS("Design", [Request Type]@row), Designer#, Developer#)

I also used @row instead of the exact row number. Here are some Help Center articles you may want to review:

Let me know if I can clarify anything further!

Cheers,

Genevieve

Paul Newcome · 02/27/20

I agree with @Genevieve P. The difference is going to be between an actual contact and a "text string". If you are unable to use the Sheet Summary section for whatever reason, you could also use a hidden "helper" column that is set to contact type and have those two contacts listed. Then you would use a cell reference in place of the Sheet Summary Field reference.

It accomplishes the same thing but is another option for those that cannot use the Sheet Summary.

nerdesigner · 02/27/20

@Genevieve P your workaround is PERFECT! Brilliant idea using Sheet summary fields.

Thank you!

Genevieve P. · 02/27/20

Happy to help! The more I use Sheet Summary, the more I love it haha.

For others who don't have this functionality, Paul's solution is the alternate way to do the same thing by using the grid in the sheet.

😊

AntonioR · 03/31/20

Hello.

Thank you @Genevieve P this help me too.

How can I get the email address(not the name of the contact) from de contact list in the summary? Since usually a contact has two values at least name and email.

Thank you again.

Genevieve P. · 04/01/20

Hi @AntonioR

What is the formula you are currently using?

If the field is a Contact List type (for both the column in the sheet and the field in the Sheet Summary) it should return the contact.

It looks like you might be referencing the wrong Summary Field... you'll want to reference "Termoformado" , the Contact field, not the one below that just has text.

AntonioR · 04/13/20

Hi @Genevieve P

This is the formula that i'm using =IF(CONTAINS("TI", Area@row), TI#, "NA")

The field is "TI", not "Termoformado"

Both field are contact list, in the summary and in the sheet.

The issue is that I have a FORM with workflow, when a new answers is sended to the sheet, the workflow sends a notification to the email that is in the field in the summary sheet. That's why I want to get the email in the sheet

Thank you!

Genevieve P. · 04/15/20

Hi @AntonioR

Very strange! If both fields are the Contact type, it should pull in the contact. You would need to make sure you're selecting the contact in the Summary Field (which it looks like you are).

The Summary Field displays contacts like text, but retains their contact information so when you pull it into a Contact Column in the actual sheet, it should bring in the full contact with the circle next to it:

Screen Shot 2020-04-15 at 1.23.35 PM.png

See how the "Email" column translates the name into the contact?

If this isn't working for you, you may want to try adding the contacts as Values to the contact column:

Screen Shot 2020-04-15 at 1.24.19 PM.png

Let me know once you've set that up if it helps. Otherwise, another option would be to adjust how your Workflows are set up, so it sends to specific email addresses when certain conditions are met.

Cheers,

Genevieve

kenny foo · 12/21/20

Hi @Genevieve P,

Will I be able to add multiple contacts under 1 summary field?

Maybe I am doing it wrong but I can't seem to do so.

Genevieve P. · 12/21/20

Hi @kenny foo

No, currently Contact Fields in the Sheet Summary section can only contain one Contact, as you've found. You would need to create multiple separate fields to display multiple contacts.

Hope that helps!

Genevieve

kenny foo · 12/21/20

https://community.smartsheet.com/discussion/comment/272678#Comment_272678

Thanks for the fast reply @Genevieve P !

Formulas and Functions

Formulas and Functions

Current user + formula in Contact List?

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary