Formula that restricts a certain value in an adjacent cells based on the value written.

[HShelp]

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.

[6StringJazzer]

Welcome to the Forum HShelp!

Please provide a complete set of rules to follow. For example, if A1=27 and A2=OFF, can A3 also be OFF?

Also, it might be possible to do this without your file but it will go much faster if you attach a sample file so we can see where everything is, and return a file with a completed solution. See yellow banner at the to of the page.

[kvsrinivasamurthy]

If data is starting from Row refer to Sheet1.
Select A1:A10. Formula for DV

Please Login or Register  to view this content.

If Data is starting after 2nd row refer Sheet2.
Select A3:A13. Formula for DV

Please Login or Register  to view this content.

In this case Ignore Blank should be unchecked.

[HShelp]

Thanks for your interest Jeff and kvsrinivasamurthy
I’ve attached an example of the roster that I’d be hoping to apply the formula to. There’d be a range of different values including cells that would contain text and numbers. I stuffed up in the original post. The roster is horizontal, not vertical, so the example should be if:

C3 = 27, D3 = OFF or 27
If C3 & D3 = 27, E3 = OFF
If C3=27, D3=OFF, E3 can = OFF, 1, 4 or 7

I hope this makes sense!

[JeteMc]

This proposal employs two conditional formatting rules
1. applied to the range D3:AR16: =AND(C3=27,D3<>27,D3<>"off")
2. applied to the range E3:AR16: =AND(C3=27,D3=27,E3<>"off")
Both rules produce red fill.
Let us know if you have any questions.