Hi,

I'm trying to come up with a formula that will help when I'm writing a roster. I want to make sure I don't accidentally put a morning shift (i.e represented by #4) immediately after an overnight shift (represented by the number #27), cap the number of overnight shifts at no more than two in a row and make only allow an off day (represented by off). I've been trying to set it up through 'data validation', but I'd also be happy to set it up as conditional formatting so that I'm alerted to it by a colour change.

For example

If cell A1 = 27, A2 can only have a 27 or an OFF, it can not have a 4. Also, A3 can only have an OFF, it cannot have a 27 or a 4.


If anyone could help I'd be very grateful! I'm starting to go cross eyed when I'm proof reading the roster.