Name - relative reference doesn't calculate

[Charles Williams]

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

 

[Bob Phillips]

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)

 

[Werner Rohrmoser]

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

 

[Charles Williams]

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]

Hi Charles,

perfect, this formula should work everywhere.
Thank you very much.

regards
Werner

 

[Lori]

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.