Check if one contacts column matches another

kagrawa

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!

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.