How to identify if Contact -OR- Text is in a Contact column?

SJ Sellers

Hi everyone -

We have starter task templates that we use when we initiate certain types of work. The templates have a Contact List column that is initially populated with ROLES (e.g. project manager, designer, tester, etc).

The ROLES are then populated by the project team with the actual CONTACT names, based on the team members of that specific project.

Question: Is there a formula I can use to test if the contents of a cell in the Contact List Column is an actual contact vs just plain text denoting the role?

Genevieve P.

Hi @SJ Sellers

Where are you looking to identify this, are you searching across sheets or within the same sheet?

There isn't a set function like ISTEXT which will identify if a value is a Contact type of value. However if you're looking at the cell, only Contacts will have the circular icon next to their name which indicates there's an underlying email address to that display text.

If you're looking across sheets, how many Role Values do you have? What I would do is build a formula to check if the cell has any of your set Role values. If it doesn't (and it's not blank), then it must be a Contact.

It would be helpful to know a bit more about your process and see some screen captures, if possible, but please block out sensitive data.

Cheers!

Genevieve

SJ Sellers

Hi Genevieve!

Sorry for the delay - I didn't see your response posted until now.

I am looking to identify Contacts within the same sheet. I am aware that the circular icon indicates Contacts - but because of the volume of rows, that won't work for my use case. I need a way to audit my project plan to quickly filter all rows (2,000 - 3,000 +) that do not have an actual person assigned to them. In this way, we can surface work that is unassigned resulting in a risk for project delivery. The end goal is to create a filter or report that surfaces all rows that still need a contact/human assigned to them.

We have close to 50 roles, so unfortunately I think creating a lookup formula will be unmanageable. I have included a screenshot.

Thanks for your help!

Genevieve P.

Hi @SJ Sellers

Thank you for this detailed explanation!

Since there's no way right now for a formula or filter to identify that plain text isn't a Contact in a contact cell, this makes it fairly tricky to achieve your end-goal. It also looks like this column could have multiple values selected, which means we can't use a Report to Group by Role and filter that way.

However, we could potentially use the multi-select to our advantage... depending on how you enter the Plain Text Roles, it's likely that this text would show up as one string if we translated it to a Multi-Select column.

Then you could use COUNTM to count how many values appear in the multi-select cell, and filter down if there's only 1 selection.

It's not fully filtering because if you only have one user assigned they will still appear, but it will eliminate any rows that have 2 or more users assigned. I hope that helps!

Cheers,

Genevieve

SJ Sellers

Hi @Genevieve P. ! Thank you for the suggestion. This doesn't get me all the way to where I need to be, but it is helpful. I appreciate the suggestion!

Thanks - SJ

joerow

I created a column called "Assigned to Contacts" as a Checkbox column. I used the following formula that will return true if one or more contacts is selected in the "Assigned To" column.

=IF(AND(COUNTM([Assigned To]@row) > 0, NOT(ISTEXT([Assigned To]@row))), true, false)

COUNTM() will count the number of selections, but will return true even if there is only text in the field.

ISTEXT() will return true if the value is only text, so the NOT() is needed since we want true only if there is not text in the field.

AND() is used to combine the conditions.

IF() is used to set the value to true or false.

Hope this helps someone!