randomly select value WITHOUT changing
 

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))


--
Doyle Brunson
------------------------------------------------------------------------
Doyle Brunson's Profile: http://www.excelforum.com/member.php...o&userid=19235
View this thread: http://www.excelforum.com/showthread...hreadid=398107

Doyle,

To my knowledge, you can't control when an Excel formula updates. You can, however, create a macro that does this. Paste the code below into VBA (ALT+F11) and it will randomly choose a number from A2:A7 and deposit the value into the currently active cell. You can link this code to a button if you like. Be sure that the sheet with the datalist is currently selected (this shouldn't be a problem if you put your button on the same page).

Here's the code:

'---------------------
Sub RandomListSelect()

ListRange = ActiveSheet.Range("A2:A7")

CountAProxy = Application.WorksheetFunction.CountA(ListRange)

ActiveCell.Value = Application.WorksheetFunction.Index(ListRange, Round(Rnd() * CountAProxy, 0))

End Sub
'---------------------

Knightly

It's working perfectly. Thank you for a useful macro.


--
Doyle Brunson
------------------------------------------------------------------------
Doyle Brunson's Profile: http://www.excelforum.com/member.php...o&userid=19235
View this thread: http://www.excelforum.com/showthread...hreadid=398107

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.


--
tx12345
------------------------------------------------------------------------
tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=398107