Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Werner Rohrmoser
 
Posts: n/a
Default 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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Charles Williams
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Frequency formula Curious Excel Worksheet Functions 1 April 12th 05 09:49 PM
Cell shows formula and not the result of the formula. stumpy1220 Excel Worksheet Functions 2 January 14th 05 06:11 PM
Cell shows formula and not the result of the formula. stumpy Excel Worksheet Functions 2 January 14th 05 05:44 PM
How do I view the actual numeric value of a formula in Excel 2002. Excel Function Help Excel Worksheet Functions 0 January 13th 05 11:07 PM
how do I make a formula NOT change when the data range is moved? Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 10:02 PM


All times are GMT +1. The time now is 12:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"