randomly select value WITHOUT changing

  • Thread starter Thread starter Doyle Brunson
  • Start date Start date
D

Doyle Brunson

I am using a formula to randomly select one of the values in a list.

However, the value returned by the formula changes with each
recalculation (F9), and I do not want that to happen.


IS THERE A WAY TO GENERATE A RANDOM VALUE FROM A LIST WITHOUT IT
CHANGING WITH EACH RECALCULATION?

The reason I ask is that I have combo boxes in the worksheet which
makes it recalculate whenever they are used.

PART 2: Is it possible to assign the formula to a button so that the
update only occours when it is pressed?

***
FYI formula used to formula to randomly select one of the values in a
list:
INDEX, ROUND, RAND, and COUNTA functions in the following formula:
=INDEX(A2:A7,ROUND(RAND()*COUNTA(A2:A7),0))
 
Hi

I tried the macro and it kept stopping. It keeps saying "Variable not
defined" while highlighting "ListRange =" in the macro.

I have

4
7
2
14
12
16

showing in A2:A7 on a blank worksheet. Not sure where the uniqe random
number is supposed to be.
 
Back
Top