Formula or Function to find a contact in a column

jake_griff250

Using the error column I am trying to use a function to find a contact in the various “Shift” Columns to the left of the day off column and have it produce an error if that employee is marked in the “day off” Column . (The point of this is to have the sheet highlight if someone is scheduled for a shift on accident while they have requested the day off) The issue is, if 2 people or more are listed in the day off column, i can not figure out a way to have the formula in the error column work. (Example in the 3rd cell down, in the “day off” column)

Sheet for Reference: https://app.smartsheet.com/b/publish?EQBCT=1f9ac38d09e349fa893ba409b27f2d2d

Sheet and picture is attached. Thanks for the help.

NicoLHC

@jake_griff250 thanks for sharing great small challenge .

Please see this formula , it works with pure text so far =IF(OR(CONTAINS([Shift 1]@row, [Day Off]@row), CONTAINS([Shift 2]@row, [Day Off]@row), CONTAINS([Shift 3]@row, [Day Off]@row)), "Yes", "No")

1. You need to create a helper column , which is a text =[Day Off]@row
2. Then add my formula + the other days shifts…..
3. We have an vacation approval system build in smartsheet maybe it would make sense to check upfront ….

General TIP , we prefer to handle Personal ID and then refer to the contact makes things easier .

If you need access to the sheet drop me a message i used your data sheet and as it contains personal informations i dont want to post it here ….. addvalue@lighthouseconsultings.com