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!
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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
-
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.
-
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).
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!