If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Name - relative reference doesn't calculate
Werner,
There are some nasty Excel bugs when Names use formulae like =!a1 If you want to use a name try this: Define a name with a refersto like this: =SUM(OFFSET(INDIRECT("RC",FALSE),0,{-3,-6,-9,-12},1,1)) If you want to use Bob's UDF you would need to bypass the Excel UDF VBE Refresh bug by making sure that calculation is ALWAYS called from VB: trap all F9, Ctrl/Alt/F9 etc with OnKey so that they call Application.Calculate etc, and calculate the Workbook in Manual. Or embed the UDF in an Automation addin. If you so this the calculation speed should be OK. Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "Werner Rohrmoser" wrote in message ... Bob, thanks for your comments, using a UDF is a good solution, but I have to use some ten thousand formulas and this would need a lot of time to calculate the book (AFAIK). Regards Werner |
#22
|
|||
|
|||
Name - relative reference doesn't calculate
Assuming too much? Maybe, maybe not. I was just pointing out that Lori's
solution does know work in the OPs situation. As to the definition of Name being correctly defined or not, that is irrelevant. It is where it is used that matters, and so as such it had serious shortcomings as a solution (FYI for the OP AND for anyone else that might Google this thread). -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... I'm assuming that Name is correctly defined and, since the OP knows what they want, they would know not to use such a formula on row 2. Maybe I'm assuming too much? -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... And it fails if in the example if name is defined as !A1:A3 then you put =SUM(Name) in A2. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "T. Valko" wrote in message ... Lori's suggestion works for me. An alternative using Bob's suggestion only with worksheet functions is to make the formula volatile: =SUM(Name)+TODAY()-TODAY() -- Biff Microsoft Excel MVP "Werner Rohrmoser" wrote in message ... Lori, I've made further tests as well and your Excel calculation description helped me to find out that there is a difference between using a named formula with relative references and to use a named relative range in a formula on a worksheet. =SUM_Something_Formula vs SUM(Something_relative_Range) The first possibility has no precednets (checked with the detective) but the 2nd one has precedents. So if you test both possibilities the first one doesn't calculate and the second one does. This has somekind of logic in connection with the Excel calculation behaviour. What's your opinion? Regards Werner |
#23
|
|||
|
|||
Name - relative reference doesn't calculate
Charles,
thank you for the formula, it works fine. One problem I have now is to get it running in different languages. Here in Germany I have to use "ZS" instead of "RC", do you know a general solution, which I can use for all countries? That would be great! (I have to send my file to 10 different countries in America, Asia and Europe) Regards Werner |
#24
|
|||
|
|||
Name - relative reference doesn't calculate
Hi Erner,
Ouch! I had not thought of that. Does this work? =SUM(OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),0 ,{-3,-6,-9,-12},1,1)) regards Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm "Werner Rohrmoser" wrote in message ... Charles, thank you for the formula, it works fine. One problem I have now is to get it running in different languages. Here in Germany I have to use "ZS" instead of "RC", do you know a general solution, which I can use for all countries? That would be great! (I have to send my file to 10 different countries in America, Asia and Europe) Regards Werner |
#25
|
|||
|
|||
Name - relative reference doesn't calculate
Hi Charles,
perfect, this formula should work everywhere. Thank you very much. regards Werner |
#26
|
|||
|
|||
Name - relative reference doesn't calculate
Werner - I'm not sure your tests are reliable. They may depend on the
order you enter formulas, e.g. if you enter the formula i proposed above and then restore the original sum(name) formula, it does recalculate ok. (Maybe something to do with the calculation tree not being rebuilt?) To be safe, I would try a solution along the lines Charles' suggested although I think you need to use N(offset(...)) to dereference the array. |
Thread Tools | |
Display Modes | |
|
|