A Challenge - Randomly Work Through a List

[modene1]

Hello,

I have two questions:

1. RANDBETWEEN()
This function automatically recalculates each time the spreadsheet recalculates. I realise I can turn off automatic calculation, however this will cause other problems.
How can I produce a random number and essentially "keep it" for use else where in the spreadsheet while still calculating and without user intervention?

2. WORK THROUGH AN ARRAY RANDOMLY
This is the problem as described in the subject line.
Say I have 5 items in a list. I want Excel to randomly work through it i.e. the user says "Give me a random item" and Excel produces #3. When the user is finished with 3, they'll need a new number. So again they say "Give me a random item" and they get 4 etc until the all 5 items have appeared.
Note that using RAND() or similar might produce item #3 2,3,4 or more times. So I need Excel to exclude previously used entries.
Finally, I am not able to use VB so I'd somehow have to create this via forumlae.

Personally, I think this is impossible, however there may be a guru out there in Wonderland who can do it!

Thanks!

 

[Special-K99]

This produces 26 random items from a list without repeating.

Random list, no duplicates
A1:A26 List of names/numbers
B1:B26 =RAND()
C1:C26 =INDEX(A$1:A$26,MATCH(SMALL(B$1:B$26,ROW()),B$1:B$26,0))

But you're probably better off using VBA.

 

[modene1]

Holy **** that works! Superb piece of coding there.
Now I'll have to dissect it to understand how.

Many many thanks
(seriously impressed)