Check if one contacts column matches another

I want to set up a notification for when a row is modified by someone NOT in a contact list column of "assignees". The idea is that if someone not in the assignees cell modifies the column, then I would notify the assignees.

I was trying to set up a helper column that would calculate if a "Last Modified By" system column matches a column that holds multiple contacts per cell.

If I have a column that allows multiple contacts and a column that has single contacts, how can I create a third column (formula) that says "put a 1 here if any name in cell A matches the one name in cell B"?

Thank you!

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

You can probably use a FIND formula instead.

This will probably help: https://community.smartsheet.com/discussion/trying-count-multiple-people-cell

Did you get it working?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I second Andree's suggestion, but I would work it backwards from what you are trying to do. Instead of seeing if any of multiple names match the single name, compare the single name to all of the multiple names. Something like this should work for you...

=IF(FIND([Single Name Column]@row, [Multi-Name Column]@row) > 0, 1)

This simply says that if the single name is found anywhere in the multi-name column, it will display a 1.

L_123

The issue is that the name isn't the same as the email. You need to create an association between the name and the email. I would do this on another sheet and call it the employee directory or something, but you could just stick it off to the right on the same sheet if you want.

Then you need to associate the modified by column email address with their name from your new database, and use the find method mentioned above to search. It would look something like

=If(or(find(index({Employee Directory Name Range},match([Modified By]@row,{Employee Directory Email Range},0)),join([Single Name Column]@row:[Multi Name Column]@row))>0,1,0)

untested potentially a typo

Paul Newcome

L@123 is correct that the name won't match the email without using a reference table, but I think the formula is a little off...

If you have 3 columns (Multi Contacts, Single Contacts, Modified By) then try this:

=IF(FIND(INDEX({Employee Directory Name Range}, MATCH([Modified By]@row, {Employee Directory Email Range}, 0)), JOIN([Single Name Column]@row:[Multi Name Column]@row)) > 0, 1, 0)

If in fact you only have 2 columns (Multi Contacts, Modified By) then give this a whirl:

=IF(FIND(INDEX({Employee Directory Name Range}, MATCH([Modified By]@row, {Employee Directory Email Range}, 0)), [Multi Name Column]@row) > 0, 1, 0)

But if it truly is what the title of your post says which is 2 different contact columns (single and multi), then you can use the formula from my first post.

kagrawa

Hi Paul,

Regarding your last comment, last sentence: Actually, I guess I was mistaken in conflating the two ideas of a "single contact column" and the "last modified by" system column. I am trying to look if any email addresses in the multiple contact column matches (or not) the single email stored in "last modified by" System column.

It seems that the System column only has an email address (even though that is an email address of a contact!). And the multiple contact column has only names (or whatever Smartsheet stores there).

So, I guess I would like a formula from Smartsheet:

GetNameOfEmail( email_address ) {

Look up email_address in Smartsheet's internal directory/directories related to this sheet (logic of this is TBD).

Return the name of that contact.

}

If I had this type of function, then I could use your initial suggestion, I think.

I would hate to re-implement an employee directory when Smartsheet already has that data available (and we already manage that data using User Management).

Paul Newcome

You are correct. You would need to create a sheet that has an employee directory. There may be a 3rd party option though, but Andree would be better than I at answering that side of things. Everything I do, I have to keep within Smartsheet.

Sign in to join the conversation!

Quick Links

Trending discussions

Contact List showing as a text only
It happened suddenly. It was working last week. I'm using Index Collect to pull a contact as cross sheet reference. But today, I did not see that working well. Usually it has circle beside my name, suddenly it vanishes, and it just contains text. I'm using those other columns to be sent notification, and it doesn't work…
Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…
Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…