Named SUM Formula with relative refernce(s)

[Werner Rohrmoser]

Dear all,

Environment: WIN XP SP1, Excel XP SP2
Subject: Named SUM Formula with relative references

Problem:
When I use a named formula like this one "=SUM(!K3:!M3)" to
sum the three cells left of "N3" the formula is calculated instantly.

When I use a named formula like this one "=SUMME(!F3;!J3;!N3;!R3)" to
to sum non continious ranges in "S3" the formula is not
calculated instantly, F9 doesn't work, only Ctrl+Alt+F9 updates "S3".

When I use a named formula like this one
"=SUMME(Sales!F3;Sales!J3;Sales!N3;Sales!R3)", including the sheet name,
it calculates instantly again.
But now this formula is limited to the sheet "Sales", which is not
what I want, because I'd like to have a global formula, which I
can use on every sheet, in every column and which recalculates instantly.

Has anyone an idea?

Best Regards
Werner

Are my findings corect, or id there a way to use Is this

 

[Dave Peterson]

xl2003 (USA settings) wouldn't let me enter:
=SUM(!K3:!M3)
I had to change it to:
=SUM(K3:M3)
(dropping the ! marks)

if you did
=summe(f3;j3;n3;r3)
does that work ok?

And make sure you have calculation set for automatic:
tools|options|calculation tab

 

[Charles Williams]

There are some Excel bugs when you use this syntax (!K3 or !$K$3) within
defined names.
I would strongly recommend that you avoid using it altogether.

An alternative is to use INDIRECT. To achieve a relative reference with
INDIRECT you have to use R1C1 notation like this
=SUM(INDIRECT("R[-12]C",FALSE),INDIRECT("R[-12]C[1]",FALSE))

the disadvantage is that INDIRECT is volatile and so gets recalculated at
every recalculation.

regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com