Current user + formula in Contact List?

nerdesigner

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

The老板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 aCurrent Userfilter, nothing shows!

I can only assume that using a formula to fill a contact list isn't compatible with theCurrent Uservalue?

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

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.

Hi@nerdesigner

If your老板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 yourSheet Summarywindow 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:

• @row function
• Sheet Summary
• Tips and Best Practices (formulas and Sheet Summary)

Let me know if I can clarify anything further!

Cheers,

Genevieve

Genevieve P.

Hi@nerdesigner

If your老板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 yourSheet Summarywindow 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:

• @row function
• Sheet Summary
• Tips and Best Practices (formulas and Sheet Summary)

Let me know if I can clarify anything further!

Cheers,

Genevieve

Paul Newcome

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

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

Thank you!

Genevieve P.

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

Hello.

Thank you@Genevieve Pthis help me too.

我怎么能得到电子邮件地址(不是th的名字吗e 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.

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

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.

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:

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 asValuesto the contact column:

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

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.

Hi@kenny foo

No, currently Contact Fields in theSheet Summary sectioncan only containoneContact, as you've found. You would need to create multiple separate fields to display multiple contacts.

Hope that helps!

Genevieve

kenny foo

Hi @kenny foo<\/a> <\/p>

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

Hope that helps!<\/p>

Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"kenny foo\",\"userID\":129999}}},{\"insert\":\" \\nNo, currently Contact Fields in the \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2476786-define-your-work-with-sheet-summary#toc-create-new-sheet-summary-fields\"},\"insert\":\"Sheet Summary section\"},{\"insert\":\" can only contain \"},{\"attributes\":{\"bold\":true},\"insert\":\"one\"},{\"insert\":\" Contact, as you've found. You would need to create multiple separate fields to display multiple contacts. \\nHope that helps!\\nGenevieve \\n\"}]","format":"rich","dateInserted":"2020-12-21T06:35:28+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-10-23T10:18:45+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/272678#Comment_272678","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/272678#Comment_272678

Thanks for the fast reply@Genevieve P!