complex validation for lottery numbers
 

I am trying to create a custom validation formula to validate a series
of six numbers that are lottery numbers.

The numbers should be whole numbers between 1 and 49 and be unique

I can do this formula to make them unique

=countif($A$3:$F$3;A3)=1

or this to make the numbers between 1 & 49

=AND(A3=1,A3<=49)

is there a way to combine these two validation rules into one which
does both or must I resort to validation using VBA?

Aman,

How about:
=AND(COUNTIF($A3:$F3,A3)<=1,A3=1,A3<=49)

The active (white) cell of your selection must be A3 for this formula.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

wrote in message
oups.com...
I am trying to create a custom validation formula to validate a series
of six numbers that are lottery numbers.

The numbers should be whole numbers between 1 and 49 and be unique

I can do this formula to make them unique

=countif($A$3:$F$3;A3)=1

or this to make the numbers between 1 & 49

=AND(A3=1,A3<=49)

is there a way to combine these two validation rules into one which
does both or must I resort to validation using VBA?

I usually don't encourage anyone to waste time using XL with lottery
numbers, but this should work:

=AND(INT(A3)=A3,A3=1,A3<=49,COUNTIF($A3:$F3,A3)=1 )


In article .com,
wrote:

I am trying to create a custom validation formula to validate a series
of six numbers that are lottery numbers.

The numbers should be whole numbers between 1 and 49 and be unique

I can do this formula to make them unique

=countif($A$3:$F$3;A3)=1

or this to make the numbers between 1 & 49

=AND(A3=1,A3<=49)

is there a way to combine these two validation rules into one which
does both or must I resort to validation using VBA?