Home 
Search 
Today's Posts 
#1




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




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: toolsoptionscalculation 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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Frequency formula  Excel Worksheet Functions  
Cell shows formula and not the result of the formula.  Excel Worksheet Functions  
Cell shows formula and not the result of the formula.  Excel Worksheet Functions  
How do I view the actual numeric value of a formula in Excel 2002.  Excel Worksheet Functions  
how do I make a formula NOT change when the data range is moved?  Excel Discussion (Misc queries) 