Roll Once

[Chris Lopeman]

Hi All,

I am new here and hope you can help. I have a slighty unusual
requirement that I must create a single formula to do the work I need.
This is because it must simply be copied and pasted, then filled
downward. This is required to keep things extermely simple. I have
my formula doing most of what I want, but I would like to add a little
persistence to it. Well kind of anyway.

To simplify I basically want to roll a dice. Then use that number in
all the cells that I filled with my formula. Right now the dice roll
is in all cells and therefore is performed again for each. Not
acceptable. I figured I could use a name check to see if it was blank
then roll if it was and set it. And if not then use it. But I see
now way to do this in a formula.

Before you say "use a macro". Have the user load it, automatically
load it, etc... Not allowed.

You help is greatly appreciated.

Thanks,

Chris

 

[Bob Phillips]

Chris,

Do you mean something like

=IF(A16="",roll_dice_formula,A16)

which assumes A16 has roll dice formula.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

The only way to do that would be to have the formula that does the roll in a
single cell, then all other cells that need to display that result or
otherwise use it would just reference that cell

A1: =formula to produce role

B9: =$A$1

F20: =$A$1

 

[Chris Lopeman]

Thanks for trying. But this does not meet the requirement of a single
formula. This is 2 formulas. Actually I don't completely understand
your example since you say A16 has the formula you seem to embed it in
the if check.

In the Excel menus there is a option Insert->Name->Define.. I was
hoping to define a name in the formula. But I cannot find a way to do
this.

Chris

 

[Chris Lopeman]

Thanks for trying. But this does not meet the requirement of a single
formula.

In the Excel menus there is a option Insert->Name->Define.. I was
hoping to define a name in the formula. But I cannot find a way to do
this.

Chris

 

[JWolf]

You found out how to do it. Just do Insert->Name->Define and paste the
formula into the refers to box.

 

[Chris Lopeman]

Thanks for the reply, but I believe you have taken my last posting out
of context.

The entire point here is to roll the dice once. If I put the whole
formula in the name, it would roll with every cell that referenced it.
I was playing with the Insert->Name->Define idea because it appeared
to be the cloest things to a sheet variable. When I was refering to
it, I meant that it would need to be done through code not the menu.
My idea in psuedo code was as follows:

If DiceResult is Defined
Then DiceResult
Else DiceResult = RandBetween(1,12)

Remember this formula will be in many cells.

Before you say 1-2 is not a pair of dice...my random number is not
really simulating dice. Its just easy to relate.

Thanks,

Chris