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
Quote:
|
Originally Posted by 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))
--
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 |