naming a formula problem

[Kade]

hello
when I name a formula (insert/name/define) then I build the formula it remembers it as absolute references.

eg if I am in A3 and I name a formula addthese then build =A1+A2 it creates this text
='Sheet1'!$A$1+'Sheet1'!$B$1

At the moment I have to then edit the text to remove the $ signs.

can I automatically make the reeference relative?

thanks kd

 

[Mark W.]

No, but you can select the formula in the Refers to field and press F4 3 times to remove all dollar signs.

 

[Aladin Akyurek]

On 2002-10-15 17:41, Kade wrote:
hello
when I name a formula (insert/name/define) then I build the formula it remembers it as absolute references.

eg if I am in A3 and I name a formula addthese then build =A1+A2 it creates this text
='Sheet1'!$A$1+'Sheet1'!$B$1

At the moment I have to then edit the text to remove the $ signs.

can I automatically make the reeference relative?

thanks kd

I'd change its def to:

=SUM(!A1:B1)

 

[Mark W.]

kd's method allows for point and click formula entry... which is fine for a small set of cells.

 

[Aladin Akyurek]

On 2002-10-15 17:53, Mark W. wrote:
kd's method allows for point and click formula entry... which is fine for a small set of cells.

Just a cautious approach... A named formula like this can be applied to any horizontal two-cell range where a "" can be available.
This message was edited by Aladin Akyurek on 2002-10-15 17:59

 

[Kade]

Thanks guys,

the F4 will do fine.

Aladin, I don't understand you comment "" what do u mean by this?. Also I agree the sum formula is better but I only used the two cell summation as a simple example

by kd

 

[Aladin Akyurek]

On 2002-10-15 23:41, Kade wrote:
Thanks guys,

the F4 will do fine.

Aladin, I don't understand you comment "" what do u mean by this?. Also I agree the sum formula is better but I only used the two cell summation as a simple example

by kd

A formula generated blank.

In A1 enter: 7

In B1 enter:

=IF(E1,F1/E1,"") which generates a blank.

In C1 enter:

=A1+B1

and observe the result.

 

[Mark W.]

Of course, =IF(E1,F1/E1,0) could be used instead, and B1 could be formatted as...

0;-0;