How do I check for repeated values in three distinct columns?

  • Thread starter Thread starter Claudio
  • Start date Start date
C

Claudio

If anyone can help me!

I have three distinct ranges that are not connected.
A2:A20
C2:C20
E2:E20

I want to be able to choose a number from 1 to 300 and place it in any
of these cells but not allowing it to repeat, giving an error message
if it does.

I know how to use this formula in the data validation to not allow a
repeat in one column:

=COUNTIF($A$2:$A$20,A2) = 1

But, when I tried different ways to include the other columns, Excel
wouldn't allow me to do it. I have Excel 2000.

Please Help me!
Claudio
 
Claudio,

With the cells selected (use Ctrl key), and active (white) cell in A2, use
this custom formula in Data Validation:

=(COUNTIF($A$2:$A$20,A2)+COUNTIF($C$2:$C$20,A2)+COUNTIF($E$2:$E$20,A2))=1
 
Claudio,

Good. Just so you'll know, your reply should be posted as a reply to mine,
not to your original post.
 
Back
Top