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,A 1)<=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.

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$1 00,A200)<=1)

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

--
Biff
Microsoft Excel MVP


"SiH23" wrote in message
...
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,A 1)<=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.