Function Question
 

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.

Function Question
 

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.