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

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 *****? 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 ***-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.

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.

"[email protected]" 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 *****? 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 ***-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.
>
>

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

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

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?

[email protected] wrote...
....
>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?

Depends whether the OP needs something for public or inhouse
distribution. There's no way to do what the OP has described without
VBA, either in the form of macros or udfs (there's a big difference -
udfs can only return values to their calling cells). If it's for
inhouse use, there are secure ways to distribute add-ins inhouse. If
it's for public distribution, there's no way for the OP to do what the
OP wants to do without requiring manual intervention from the user. Up
to the OP to decide whether to do this or not.

Besides, given the current zero day trojans targetting Excel, just how
safe is supported software shipped by the largest software manufacturer
in the world?

If you want safety, disconnect.