Duplicate values
 

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

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

"Louise" wrote:

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Louise" wrote in message
...
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

"Louise" wrote:

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

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

Louise

"Max" wrote:

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Louise" wrote in message
...
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

"Louise" wrote:

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

Error in pasting, sorry

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

should read:
Formula: =COUNTIF($1:$65536,A1)<2
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Works a treat!!! Thanks very much.

Have a good weekend.

Louise

"Max" wrote:

Error in pasting, sorry

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

should read:
Formula: =COUNTIF($1:$65536,A1)<2
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Glad it worked !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Louise" wrote in message
...
Works a treat!!! Thanks very much.

Have a good weekend.

Louise