A data validation question

[Portunus]

I'm trying to understand how/why Excel does this and would like to
know if there is a way to do this differently. Here is my scenario

Column-A contains numeric data
1
0
2
0
3
4
5

Column-B is a "custom" data validated field with the following
formula: =OR(AND(A1=0,B1=0),AND(A1>0,B1>=2))

If I input a value to Column-B-1 >=2 the value is accepted.

If I drag the cell (from bottom right) to fill down rows the value
from Column-B-1 is "copied" as well.

I understand this. What I can't understand is the value it copies
violated the data validation formula (no error is returned) and then I
must manually enter the acceptable value, 0, into Column-B-2.

Is there a way to either make the value of Column-B-2 a 0 (zero) or an
empty cell on a drag/fill operation?

Thanks

 

[witek]

I'm trying to understand how/why Excel does this and would like to
know if there is a way to do this differently. Here is my scenario

Column-A contains numeric data
1
0
2
0
3
4
5

Column-B is a "custom" data validated field with the following
formula: =OR(AND(A1=0,B1=0),AND(A1>0,B1>=2))

If I input a value to Column-B-1 >=2 the value is accepted.

If I drag the cell (from bottom right) to fill down rows the value
from Column-B-1 is "copied" as well.

I understand this. What I can't understand is the value it copies
violated the data validation formula (no error is returned) and then I
must manually enter the acceptable value, 0, into Column-B-2.

Is there a way to either make the value of Column-B-2 a 0 (zero) or an
empty cell on a drag/fill operation?

Thanks

data is checked against validation rule only if it is entered "by keyboard".
If you paste, drag or uses vba to enter value in a cell nothing is
validated.
Ask Microsoft why.

you can use vba code to check values in worksheet_change event