RANDBETWEEN query

[scottwilsonx]

Hi everyone, hope someone can help.
I have a list of 16 names, numbered 1 to 16 in the range: A1 to B1
where column A has the names, and column B the sequence from 1 to 16.

I have a cell with the formula: RANDBETWEEN(1,16) which gives you
number between 1 and 16 each time you press F9 to recalculate.

However, I would like to change the formulae so that if you recalculat
and get the number 6 (for example), you won't get 6 again until th
other 15 numbers have also been shown.

Is this possible using a function or VBA ?

Many thanks for your help

 

[Frank Kabel]

Hi
see:
http://www.mcgimpsey.com/excel/udfs/randint.html

 

[hgrove]

Frank Kabel wrote...

see:
http://www.mcgimpsey.com/excel/udfs/randint.html

...

Incomplete answer. Randint could provide a shuffled array of number
from 1 to 16, but to sample from it cyclically would require anothe
UDF like

Function cycrnd() As Long
Static a As Variant, n As Long

Application.Volatile 'necessary for this

If IsEmpty(a) Then
a = randint(1, 16)
n = LBound(a)

Else
If n < UBound(a) Then n = n + 1 Else n = LBound(a)

End If

cycrnd = a(n)

End Functio