Duplicate values

[Guest]

How do I set a formula in Excel so it will not allow me to enter duplicate
values on a worksheet? I have used the Data Validation tool before to set
the countif function on a particular column but how do I set it for the whole
worksheet?

Thank you.

Louise

 

[Guest]

Update: I have entered a formula of =(countif(A:AA,A1)<2).
This works but only if the duplicate occurs in the same column. If I enter
24 in column A and 24 in Column D, it doesn't pick them up.

What am I doing wrong???

Thank you.

Louise

 

[Max]

It seems to work ok when I tried this ..

In a new sheet,
press CTRL+A
(this selects the entire sheet)

Click Data > Validation
Allow: Custom
Formula: =COUNTIF($1:$65536,A65494)<2
Click OK

 

[Guest]

Thanks for your quick reply. However, I've tried this and it hasn't done
anything? It is still allowing duplicate entries??

Louise

 

[Max]

Error in pasting, sorry

Line

Formula: =COUNTIF($1:$65536,A65494)<2

should read:

 

[Guest]

Works a treat!!! Thanks very much.

Have a good weekend.

Louise

 

[Max]

Glad it worked !
Thanks for the feedback ..