Can I do two types of data validation on the same cells?

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

Claudio

Ok, I need help again!

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

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

1) I know I can use a List in the Data Validation with the source being
a list from 1-300 in another column. This will only allow the user to
enter a number between 1-300 and no other, but it will allow repeats.

2) I know I can 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

to not have any number repeat in these three columns.

But, how do I get Excel to carry out these two reaquirements at the
same time? Can I do two types of Data Validation on the same cells or
do I need VBA?

I know very little about VBA, but I'm willing to hear any suggestions.

Thank You
Claudio
 
=AND(A2>=1,A2<=300,(COUNTIF($A$2:$A$20,A2)+COUNTIF($C$2:$C$20,A2)+COUNTIF($E
$2:$E$20,A2))=1)
 
Back
Top