Remember Me?

#1
 Werner Rohrmoser Posts: n/a
Named SUM Formula with relative refernce(s)

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
#2
 Dave Peterson Posts: n/a

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

Werner Rohrmoser wrote:

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
#3
 Charles Williams Posts: n/a

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

"Werner Rohrmoser" wrote in message
m...
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Curious Excel Worksheet Functions 1 April 12th 05 09:49 PM stumpy1220 Excel Worksheet Functions 2 January 14th 05 06:11 PM stumpy Excel Worksheet Functions 2 January 14th 05 05:44 PM Excel Function Help Excel Worksheet Functions 0 January 13th 05 11:07 PM Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 10:02 PM

All times are GMT +1. The time now is 12:20 AM.