Roll Once
 

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

Roll Once
 

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)

"Chris Lopeman" wrote in message
om...
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

Roll Once
 

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

--
Regards,
Tom Ogilvy

"Chris Lopeman" wrote in message
om...
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

Roll Once
 

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

"Bob Phillips" wrote in message ...
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)

"Chris Lopeman" wrote in message
om...
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

Roll Once
 

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

"Tom Ogilvy" wrote in message ...
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

--
Regards,
Tom Ogilvy

"Chris Lopeman" wrote in message
om...
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

Roll Once
 

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

Chris Lopeman wrote:
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

"Bob Phillips" wrote in message ...

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)

"Chris Lopeman" wrote in message
.com...

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

Roll Once
 

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

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

Chris Lopeman wrote:
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

"Bob Phillips" wrote in message ...

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)

"Chris Lopeman" wrote in message
.com...

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