Way to do data validation on cells depending on adjacent cells?

**jshrader** · 11-20-2016, 01:00 AM

Hello. I have a sheet that track employee absenteeism. I have tabs for each month that lists each employee with adjacent columns for each day of the month. For each day out of the office, we use a code (vacation, sick, bereavement, company holiday, etc.) and we want to compare those codes to some business rules. The rules are things like:
1) you cannot call in sick the day before vacation or a company holiday (so if the company holiday, "h" is on the 7th of the month, then I want to get notification that a sick ("s") day was entered on the 6th); or
2) when there is bereavement time, an employee gets 2 days per incident (so if "b" is entered on the 15th, then it would be allowed on the 16th, but when the 17th is attempted, the it would not be allowed).

It would be great if I could not enter the data that would break the rules (similar to data validation) but it would be fine if this would show is another cell if these rules are broken.

Thoughts?

Thank you in advance!

**FDibbins** · 11-20-2016, 02:37 AM

Can you show some sample data, and what you expect?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**jshrader** · 11-20-2016, 03:07 PM

Thank you for your question FDIbbins!

I have attached a file per you request. If it was only one cell to check, then I think I could handle with variations of if(and/or but I am a bit confused since I have to check every cell in the month. Thoughts?