software

Hi.

I am using the formula:
=INDEX(A356:A377,ROUND(RAND()*COUNTA(A356:A377),0) )

To generate a random name from a list. It works fine, but everytime I
do something else on the spreadsheet it generates another random name
form the list. I want it to generate the name once, that is all and
not keep doing it.

Can someone help? Tete--
teteperreira
------------------------------------------------------------------------
teteperreira's Profile: www.excelforum.com/member.php...oamp;userid=34280
View this thread: www.excelforum.com/showthread...hreadid=540443quot;teteperreiraquot; wrote:
gt; I am using the formula:
gt; =INDEX(A356:A377,ROUND(RAND()*COUNTA(A356:A377),0) )
gt; To generate a random name from a list. It works fine, but
gt; everytime I do something else on the spreadsheet it generates
gt; another random name form the list.

And every time you change some other worksheet in the same
workbook. Klunk!

gt; I want it to generate the name once, that is all and not keep
gt; doing it. Can someone help?

Y'mean you do not see the obvious benefit and rationale for
making RAND() a volatile function!? (Dripping in sarcasm.)

Presumably, you also do not want to use the F9 trick explained
on the RAND() help page, which replaces the cell formula with
its result -- perhaps because you might want to generate new
random results later at __your__ discretion, or at the very least
you want to see the formula that you used. (What a concept!
Drip, drip, drip ....)

Presumably you also don't think it is reasonable to simply
enable manual calculation (Tools gt; Options gt; Calculation) --
oh, and don't forget to disable quot;recalculate before savequot;.
How unreasonable of you! (Dripping in sarcasm again.)

My solution is to create a macro, cleverly named myrand(),
which simply returns Rnd(), a VBA function. I even define an
optional parameter so that myrand() can be recomputed if
a change is made to a dependent cell or range.

Of course, that means that your worksheet now has at least
one macro -- an inconvenience that requires you and your
users choose an appropriate macro security level.

Also, the solution is not perfect. myrand() is still recalculated
whenever I delete a worksheet from the workbook -- unless
I disable auto calc before deleting the worksheet. (Office
Excel 2003.)

(Even with the latter precaution, myrand() is recalculated if
I re-enable auto calc after deleting the worksheet. Sigh.)

I probably should also point out that the VBA Rnd() function
is not exactly the same as the Excel RAND() function. At least,
not externally. VBA Rnd() returns a single-precision floating
point value, whereas I ass-u-me that Excel RAND() returns a
double-precision floating pointer value. I think you would be
hard-pressed to see the difference; but it is possible. Besides,
I have no idea where or not the internal algorithms are the
same and, if not, which is better. __That__ could make a
noticable difference in some circumstances, albeit perhaps not
yours.

HTH. I would welcome feedback from MVPs about my approach.