Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
finding common numbers in large lists | Excel Worksheet Functions |