In this reply from Max to Steved re lotto numbers
I am curious to find out how Pressing F9 will recalculate the numbers.
Here's an adaptation of Harlan's solution to the
OP's pick 6 out of 40 case
Define* a name: LottoNumbers
referring to: =ROW(INDIRECT("1:40"))
*via Insert Name Define
Then in A1 enter the formula:
=INT(1+COUNT(LottoNumbers)*RAND())
In B1, enter the array formula:
=LARGE(IF(COUNTIF($A1:A1,LottoNumbers),0,LottoNumb ers),
INT(1+(COUNT(LottoNumbers)-COUNT($A1:A1))*RAND()))
Copy B1 to F1
Select A1:F1, fill down to say, F10
A1:F10 will return 10 sets of 6 numbers (from 1-40)
w/o duplicates in any one set
*Press F9 to recalc* --
Rgds
Max
--
redchequer
------------------------------------------------------------------------
redchequer's Profile:
http://www.excelforum.com/member.php...o&userid=17396
View this thread:
http://www.excelforum.com/showthread...hreadid=466421