I am trying to create data validation for a range of cells, used for our timesheets - the idea is that the cell can be any number divisible by 0.25, or a phrase e.g. OFF, SICK, etc.

Using the custom formula =E12="OFF" - as expected, I can put OFF in the cell.

Using the custom formula =MOD(E12,0.25)=0 - as expected, I can put any number divisible by 0.25.

Following this logic, using =OR(E12="OFF",MOD(E12,0.25)=0) - I would expect to be able to have either OFF or any number divisible by 0.25 - the numbers are fine but it will not allow "OFF".

On the other hand, =OR(E12="OFF",E12="SICK") - both phrases work and nothing else.

Also, =OR(MOD(E12,0.25)=0,E12=1.1) - I can put anything divisible by 0.25 or the value 1.1.

So I can't understand why a mix of both the modulus as well as specific phrases will not work as expected.

Any help would be greatly appreciated.

N.B. - Overall, ideally, I would like 0 < E12:E30 < 12 as well as E12:E30 to be able to be "OFF", "SICK", "SMP", "SPP", "HP".

CodePudding user response：

You will get an error on MOD(E12,0.25)=0 when the cell is text and an error will cause the data validation to become FALSE overall, so you need to split the path:

=IF(ISNUMBER(E12),MOD(E12,0.25)=0,E12="OFF")

What you want Overall:

 =IF(ISNUMBER(E12),OR(MOD(E12,0.25)=0,AND(E12>0,E12<12)),ISNUMBER(MATCH(E12,{"SICK", "SMP", "SPP", "HP"},0)))