random numbers

This generates a list of numbers from 1 to 20 in random order on sheet 2. Not sure if it is what you want, but it might help

Public Sub main()
  Dim iRandArray() As Integer
  Dim i As Integer
  RandomOrder 20, iRandArray()
  For i = 1 To UBound(iRandArray)
    Sheets("Sheet2").Range("A1").Offset(i, 0).Value = iRandArray(i)
  Next
End Sub

Private Sub RandomOrder(ByVal iNumRange As Integer, ByRef iRandArray() As Integer)


  Dim iNumArray() As Integer
  Dim iRandValue As Integer
  
  ReDim iNumArray(1 To iNumRange, 1 To 2)
  ReDim iRandArray(1 To iNumRange)
  
  For i = 1 To iNumRange
    iNumArray(i, 1) = i
    iNumArray(i, 2) = 1
  Next
  
  For i = 1 To iNumRange
    Randomize
    iRandValue = Int((iNumRange * Rnd) + 1)
    
    If iNumArray(iRandValue, 2) = 0 Then
      i = i - 1
    Else
      iRandArray(i) = iRandValue
      iNumArray(iRandValue, 2) = 0
    End If
  Next


End Sub

Here's a formula you can use. In this example, enter the numbers 1 thru 20 in cells A1:A20 on Sheet2 and in cells B1:B20 enter the formula =Rand()

Then use this formula:
=INDEX(Sheet2!A1:A20,RANK(Sheet2!B1,Sheet2!B1:B20))

You'll want to set the range references to absolute when you fill the formula down:

=INDEX(Sheet2!$A$1:$A$20,RANK(Sheet2!B1,Sheet2!$B$1:$B$20))

Re: random numbers

This is *possibly* a simpler way...

In cells A1:A20 on Sheet2 put the formula;

=RAND()

In cell A1 on Sheet1 put this formula;

=RANK(Sheet2!A1,Sheet2!$A$1:$A$20)

Then copy cell A1 on Sheet1 thru to cell A20 on Sheet1.

Good Luck!
-roadmax

Re: random numbers

Maybe this link will help ?

http://www.ozgrid.com/Excel/excel-random-pick.htm

Jack