Function Question

[SiH23]

I wonder whether someone could help?

The following function when used inconjunction with Data Validation will
allow only a 9 digit number to be entered into a cell with no duplicate
values:

=AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$100,A1)<=1)

However, when I select a range of cells outside of the 'COUNTIF' range; for
example up to A200 the function still works. Why is this when the cell does
not fall within the range A1:A100?

Kind regards,

Simon.

 

[T. Valko]

Because ISNUMBER, LEN and the COUNTIF criteria reference are all relative
references. If you applied that validation in A200 this is what the formula
looks like:

=AND(ISNUMBER(A200),LEN(A200)=10,COUNTIF($A$1:$A$100,A200)<=1)

Notice the COUNTIF range reference is still absolute to A1:A100.