Custom Validation Not Accepting Valid Data Revision

T

testspecmed

Does anyone know why the following custom validation formula does NOT
allow
the user to accept zero (0) as a valid replacement for the target
cell?:

S11=AND(COUNTA($P11:$S11)<3)

MS-Excel will allow the user to hit the "escape" key and reset the
target field
value back to "blank" status.
 
D

Debra Dalgleish

A zero is counted by the COUNTA function, and a blank cell is not
counted. If there are already two entries in the range P11:S11, the
formula won't allow any entry, even a zero, in the cell with validation.

The formula doesn't need the AND function though, and could be
simplified to:

=COUNTA($P11:$S11)<3
 
Top