Contact List formatting from formula only showing email address

I've got a formula that pulls contacts from another reference sheet into a Contact List column, but it also checks an adjacent column for a value to transpose a would-be contact:

=IFERROR(IF(Resource@row = "Eric R.", "erussell@email.com", INDEX(COLLECT({IT Requisition Tasks Resource}, {IT Requisition Tasks Task Name}, [Item Description]@row, {IT Requisition Tasks ID}, [IT Req]@row), 1))), "")

However, the input from the if true (adjacent column) is added to the Contact List column only showing the email address, though it looks correct if you hover over it (see below).

You can also see the rows below pulling from the latter half of the IF statement which pulls in a value from the contact list on the reference sheet. This is pulling in with the correct format. Only the formula attempting to input the contact column value shows the unintended formatting in the cell.

I've also tried the following to input the contact value from the formula, but the formula above is the closest while the following do not trigger the addition of the contact badge in the cell:

IF(Resource@row = "Eric R.", "Eric Russell <erussell@email.com>","")

IF(Resource@row = "Eric R.", JOIN("Eric Russell","<erussell@email.com>"),"")

IF(Resource@row = "Eric R.", "Eric Russell" + "<erussell@email.com>","")

Find more posts tagged with

Contacts

Formulas

Accepted answers

Genevieve P.

Hi @bsim1130

Here's the workflow I have set up. You could change this to have multiple conditions filtering to different actions if there will be other selections in the "Resource" column.

Screen Shot 2021-03-09 at 1.42.32 PM.png

This will automatically bring in the correct contact based on the text value in the Resource Column:

Screen Shot 2021-03-09 at 1.44.42 PM.png

Since it's bringing in a contact value, this will have the contact name and email. Then your formula will reference this cell instead of "text":

[Helper Contact]@row

First it will check if the cell is empty, and if it is, complete the INDEX(COLLECT you have written. But if it's not empty, it will copy the exact contact over:

Screen Shot 2021-03-09 at 1.45.18 PM.png

You can hide the Helper Contact if that helps! Or, depending on your process, you may be able to use the Assign People automation with other criteria to eliminate the need for any formula at all.

Let me know if you need any further clarification.

Cheers!

Genevieve

All comments

Genevieve P.

Hi @bsim1130

The way you have your initial formula is correct; formulas can't recognize the full contact in the same way that the column can. It will bring in the email address without the name, but it will still be a contact-type of value (so it can be used in automations, etc).

If it's important to have the name instead of the email, I think we may be able to create a different way of pulling in this information by using the new Assign People automation.

You could have a Helper Column set as a Contact List type of column. Then in the Workflow, set up either the trigger or a condition to be that "Eric R." is in the Text column, and assign Eric's contact to the Helper Column if this is true... or assign different other people based on this resource text column. (See: Assign People to a Task)

Then in your formula you can reference this Helper cell instead of using the email address "in quotes". You would say that if the Resource column is blank, do the INDEX(COLLECT formula. If it's not blank, pull through the contact in the helper column.

=IFERROR(IF(Resource@row = "", INDEX(COLLECT({IT Requisition Tasks Resource}, {IT Requisition Tasks Task Name}, [Item Description]@row, {IT Requisition Tasks ID}, [IT Req]@row), 1)), [Helper Contact]@row), "")

By referencing a cell with the contact in it, this will bring through the full contact format (name and email). Does that make sense?

Let me know if you'd like to see screen capture examples of my suggestion above!

Cheers,

Genevieve

bsim1130

Thanks for the reply. I do think some screenshots would be helpful, though I believe I understand what you're proposing.

I try to construct our sheets with minimal columns and maximum efficiency. In my opinion, this is what formulas are for. However, to accomplish some seemingly basic tasks, I do end up having to create an uncomfortable amount of "helper columns".

Genevieve P.

Hi @bsim1130

Here's the workflow I have set up. You could change this to have multiple conditions filtering to different actions if there will be other selections in the "Resource" column.

This will automatically bring in the correct contact based on the text value in the Resource Column:

Since it's bringing in a contact value, this will have the contact name and email. Then your formula will reference this cell instead of "text":

[Helper Contact]@row

First it will check if the cell is empty, and if it is, complete the INDEX(COLLECT you have written. But if it's not empty, it will copy the exact contact over:

You can hide the Helper Contact if that helps! Or, depending on your process, you may be able to use the Assign People automation with other criteria to eliminate the need for any formula at all.

Let me know if you need any further clarification.

Cheers!

Genevieve

bsim1130

There's this phrase: "Go around the world to get across the street." Though we take the scenic route, I do believe we eventually get across the street with this LOL.

Many thanks, @Genevieve P.

Genevieve P.

Haha, no problem! Glad we could get there in the end. 🌏️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.