How do I generate only one random number without it refreshing?
 

I need to have one cell in a sheet generate a random number without changing
after calculations are entered in other cells.

What I've tried so far:
=randbetween(111111,999999)

But this number keeps changing every time any other data is entered into
other cells in the sheet, or a calculation processes.

So, I disabled calculations, but that won't work b/c I need to have the
calculations available.

Oh, and I can't use macros b/c the sheet is going to be posted on the net.

Any suggestions for generating one random number and keeping it? Thanks

How do I generate only one random number without it refreshing?
 

joshman wrote:
What I've tried so far:
=randbetween(111111,999999)

But this number keeps changing every time any other data is entered into
other cells in the sheet, or a calculation processes.

Isn't that a bitch? I don't know what was going in the head of the kid
who decided that rand() and related functions should be volatile.

So, I disabled calculations, but that won't work b/c I need to have the
calculations available.

That should come as no surprise to anyone, except the aforementioned
kid, that is.

Oh, and I can't use macros b/c the sheet is going to be posted on the net.

Oops, now you've exhausted the only remedy I know. OTOH, I know of
plenty of spreadsheets available online that do indeed rely on a macro
to force a more reasonable behavior of random number generation. You
simply have to encourage your customers to select a reasonable macro
security level and trust you. I know: trust is difficult to earn.
But frankly, I would not trust just-any-spreadsheet-on-the-net anyway,
with or without macros. The virus inventors are just too clever for me
to take the chance.

Any suggestions for generating one random number and keeping it?

I ass-u-me you really need randbetween() or its ilk because
__sometimes__ you do want the random number to recalculate when the
spreadsheet is in someone else's hands. Right?

If not, of course you could simply copy-and-paste-special-value. But
that's too obvious, and you sound savvy enough. I am sure it does not
meet your needs.

IMHO, it is about time that we petition MS for an option (Tools
Options) to make rand() and related functions non-volatile, "breaking"
compatibility (to everyones delight, I'm sure). Don't get me wrong:
backward compatibility is a strong argument. But there is no good
argument against yet-another option. MS could even allow it to default
to its current moronic behavior.

Good luck in your quest for a more constructive response. I will be
watching this thread for it myself.

How do I generate only one random number without it refreshing
 

major bites my butt...
I would go for a macro but I'm at a university where half of the people
working here are scared of their mice....if they were to see the "This
spreadsheet has macros that might tear the fabric of space and time" warning
they'd probably curl up under their desks and pray for a miracle.

Maybe someone, somewhere has an answer. Thanks for the reply joeu.

" wrote:

joshman wrote:
What I've tried so far:
=randbetween(111111,999999)

But this number keeps changing every time any other data is entered into
other cells in the sheet, or a calculation processes.

Isn't that a bitch? I don't know what was going in the head of the kid
who decided that rand() and related functions should be volatile.

So, I disabled calculations, but that won't work b/c I need to have the
calculations available.

That should come as no surprise to anyone, except the aforementioned
kid, that is.

Oh, and I can't use macros b/c the sheet is going to be posted on the net.

Oops, now you've exhausted the only remedy I know. OTOH, I know of
plenty of spreadsheets available online that do indeed rely on a macro
to force a more reasonable behavior of random number generation. You
simply have to encourage your customers to select a reasonable macro
security level and trust you. I know: trust is difficult to earn.
But frankly, I would not trust just-any-spreadsheet-on-the-net anyway,
with or without macros. The virus inventors are just too clever for me
to take the chance.

Any suggestions for generating one random number and keeping it?

I ass-u-me you really need randbetween() or its ilk because
__sometimes__ you do want the random number to recalculate when the
spreadsheet is in someone else's hands. Right?

If not, of course you could simply copy-and-paste-special-value. But
that's too obvious, and you sound savvy enough. I am sure it does not
meet your needs.

IMHO, it is about time that we petition MS for an option (Tools
Options) to make rand() and related functions non-volatile, "breaking"
compatibility (to everyones delight, I'm sure). Don't get me wrong:
backward compatibility is a strong argument. But there is no good
argument against yet-another option. MS could even allow it to default
to its current moronic behavior.

Good luck in your quest for a more constructive response. I will be
watching this thread for it myself.

How do I generate only one random number without it refreshing
 

joshman wrote:
major bites my butt...
I would go for a macro but I'm at a university where half of the people
working here are scared of their mice....if they were to see the "This
spreadsheet has macros that might tear the fabric of space and time" warning
they'd probably curl up under their desks and pray for a miracle.

Geesh, the people at your university are m-u-c-h wiser than my (now
post-)college kids. My kids view such warnings as an invitation to "go
for broke" -- much like people who ignore "no turn on red". (Crash!)
My computers became so virus-infected that on my newer computers, I
password my admin accounts and set up restricted accounts for the kids.
They can screw up their own computers, but not mine.

PS: I'm not scared of my mouse, but I am scared of my touchpad. It
seems to have a mind of its own :-).

How do I generate only one random number without it refreshing?
 

joshman wrote...
I need to have one cell in a sheet generate a random number without changing
after calculations are entered in other cells.

What I've tried so far:
=randbetween(111111,999999)

But this number keeps changing every time any other data is entered into
other cells in the sheet, or a calculation processes.

So, I disabled calculations, but that won't work b/c I need to have the
calculations available.

Oh, and I can't use macros b/c the sheet is going to be posted on the net.

Any suggestions for generating one random number and keeping it? Thanks

What's the purpose of this random integer? There may be alternatives.

Anyway, RANDBETWEEN is an add-in function. Do you users have no
problems loading add-ins? If that's the case, use another add-in to
hold a udf like


Function srb(a As Long, b As Long, Optional v As Boolean) As Long
Static s As Boolean, n As Long
Randomize
If v Or Not s Then
n = CLng(a + (b - a + 1) * Rnd)
s = True
End If
srb = n
End Function


Use it in formulas like

=srb(11111,99999,COUNT(_REEVALUATE_))

If the defined name _REEVALUATE_ doesn't exist or doesn't evaluate to a
number, the COUNT call return 0, which VBA converts to FALSE, in which
case srb always returns the same thing. Define _REEVALUATE_ as any
number, and srb will change on any full recalc ([Ctrl]+[Shift]+[F9])
but not on minimal recalc ([F9]).

How do I generate only one random number without it refreshing?
 

Harlan Grove wrote:
RANDBETWEEN is an add-in function. Do you users have no
problems loading add-ins? If that's the case, use another add-in to
hold a udf like

I'm sure even an unsavvy user understands the difference between a
supported add-in shipped by the largest software manufacturer in the
world with one of the most widely used software products in the world
versus an add-in provided by any "Joe User" with no credentials
whatsoever. Why would I think that a UDF in an add-in (other than one
provided by a company like MS) is any safer to import than a macro?