Name - relative reference doesn't calculate
 

Hi,

I use a mane with a relative reference to sum 3 cells to the left or
in another case
the 3rd, 6th, 9th and 12th cell to the left.
The syntax for the name is "!A1:A3" when my pointer is in A4, formula
is "=SUM(Name). I wrote it without the sheetname, because I'd like to
use it on every sheet I have in the book.
Now, when I change values in the precedent cells nothing happens until
I force a complete recalculation,
it doesn't calculate automatically, also F9 doesn't force the formula
to calculate.
Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th"
case.

Excel XP SP3
Win XP SP1


Regards
Werner

Name - relative reference doesn't calculate
 

One of the big problems with using that syntax.

Why don't you write a UDF, you can add error checking easily then (when
there aren't 3 for instance)?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Werner Rohrmoser" wrote in message
...
Hi,

I use a mane with a relative reference to sum 3 cells to the left or
in another case
the 3rd, 6th, 9th and 12th cell to the left.
The syntax for the name is "!A1:A3" when my pointer is in A4, formula
is "=SUM(Name). I wrote it without the sheetname, because I'd like to
use it on every sheet I have in the book.
Now, when I change values in the precedent cells nothing happens until
I force a complete recalculation,
it doesn't calculate automatically, also F9 doesn't force the formula
to calculate.
Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th"
case.

Excel XP SP3
Win XP SP1


Regards
Werner

Name - relative reference doesn't calculate
 

On Jan 28, 10:53 am, Werner Rohrmoser
wrote:
Hi,

I use a mane with a relative reference to sum 3 cells to the left or
in another case
the 3rd, 6th, 9th and 12th cell to the left.
The syntax for the name is "!A1:A3" when my pointer is in A4, formula
is "=SUM(Name). I wrote it without the sheetname, because I'd like to
use it on every sheet I have in the book.
Now, when I change values in the precedent cells nothing happens until
I force a complete recalculation,
it doesn't calculate automatically, also F9 doesn't force the formula
to calculate.
Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th"
case.

Excel XP SP3
Win XP SP1

Regards
Werner


Hi

Have you checked the settings for recalculation in your sheet?
ToolsOptionsCalculation tab and ensure that 'Automatic' is checked.
Sorry if that's a daft question.

Regards,
Matt Richardson
http://teachr.blogspot.com

Name - relative reference doesn't calculate
 

Maybe try:

=SUM(A:A Name)

which will force a reference to the activesheet and should autocalc.

On 28 Jan, 10:53, Werner Rohrmoser
wrote:
Hi,

I use a mane with a relative reference to sum 3 cells to the left or
in another case
the 3rd, 6th, 9th and 12th cell to the left.
The syntax for the name is "!A1:A3" when my pointer is in A4, formula
is "=SUM(Name). I wrote it without the sheetname, because I'd like to
use it on every sheet I have in the book.
Now, when I change values in the precedent cells nothing happens until
I force a complete recalculation,
it doesn't calculate automatically, also F9 doesn't force the formula
to calculate.
Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th"
case.

Excel XP SP3
Win XP SP1

Regards
Werner

Name - relative reference doesn't calculate
 

Sorry to bother this way Bob, but your site seems to be offline

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bob Phillips" wrote in message ...
| One of the big problems with using that syntax.
|
| Why don't you write a UDF, you can add error checking easily then (when
| there aren't 3 for instance)?
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my addy)
|
|
|
| "Werner Rohrmoser" wrote in message
| ...
| Hi,
|
| I use a mane with a relative reference to sum 3 cells to the left or
| in another case
| the 3rd, 6th, 9th and 12th cell to the left.
| The syntax for the name is "!A1:A3" when my pointer is in A4, formula
| is "=SUM(Name). I wrote it without the sheetname, because I'd like to
| use it on every sheet I have in the book.
| Now, when I change values in the precedent cells nothing happens until
| I force a complete recalculation,
| it doesn't calculate automatically, also F9 doesn't force the formula
| to calculate.
| Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th"
| case.
|
| Excel XP SP3
| Win XP SP1
|
|
| Regards
| Werner
|
|

Name - relative reference doesn't calculate
 

Matt,

automatic calculation is checked.

Regards
Werner

Name - relative reference doesn't calculate
 

Lori,

my intention is to use a general formula in order to sum 3 cells to
the left of the active cell
and this formula should work on every sheet (I need this type of
formula some hundred times on some 20 sheets).
So I have learned from John Walkenbachs Formula Book, that I have to
use a relative name like ="!A1:!C1",
when my active cell is "D1" and to write D1=SUM(Name). This type of
formula recalculates automatically.
But when I use this expression for a relative name "!A1,!C1,!
E1" (every second cell) and my active cell is "F1"
than it only recalculates when I use "Ctrl Alt F9". I know this but my
user don't know it.
This behaviour is strange and I don't understand it.

Regards
Werner

Name - relative reference doesn't calculate
 

Excel uses a calculation algorithm where formulas recalculate only
when the precedent cells are changed but since no sheet reference is
given in the name, the named cells are not counted as precedents.

However if you include a specific reference to the same row or column
in the formula, then any change in that row or column should trigger a
recalc. So for your example with cells in the first row try:

=SUM(1:1 Name)

and then copy these formulas down and across sheets.

works in my tests

On 28 Jan, 14:35, Werner Rohrmoser
wrote:
Lori,

my intention is to use a general formula in order to sum 3 cells to
the left of the active cell
and this formula should work on every sheet (I need this type of
formula some hundred times on some 20 sheets).
So I have learned from John Walkenbachs Formula Book, that I have to
use a relative name like ="!A1:!C1",
when my active cell is "D1" and to write D1=SUM(Name). This type of
formula recalculates automatically.
But when I use this expression for a relative name "!A1,!C1,!
E1" (every second cell) and my active cell is "F1"
than it only recalculates when I use "Ctrl Alt F9". I know this but my
user don't know it.
This behaviour is strange and I don't understand it.

Regards
Werner

Name - relative reference doesn't calculate
 

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

Name - relative reference doesn't calculate
 

Hi Niek,

I got an email today saying I had exceeded my monthly bandwidth, so my host
has disabled it. It must be getting popular <g. I'll have to check out how
much I need to pay to increase it.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Niek Otten" wrote in message
...
Sorry to bother this way Bob, but your site seems to be offline

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bob Phillips" wrote in message
...
| One of the big problems with using that syntax.
|
| Why don't you write a UDF, you can add error checking easily then (when
| there aren't 3 for instance)?
|
| --
| ---
| HTH
|
| Bob
|
|
| (there's no email, no snail mail, but somewhere should be gmail in my
addy)
|
|
|
| "Werner Rohrmoser" wrote in message
| ...
| Hi,
|
| I use a mane with a relative reference to sum 3 cells to the left or
| in another case
| the 3rd, 6th, 9th and 12th cell to the left.
| The syntax for the name is "!A1:A3" when my pointer is in A4, formula
| is "=SUM(Name). I wrote it without the sheetname, because I'd like to
| use it on every sheet I have in the book.
| Now, when I change values in the precedent cells nothing happens until
| I force a complete recalculation,
| it doesn't calculate automatically, also F9 doesn't force the formula
| to calculate.
| Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th"
| case.
|
| Excel XP SP3
| Win XP SP1
|
|
| Regards
| Werner
|
|

Name - relative reference doesn't calculate
 

Build a uDF as I suggested earlier.

Function Sum3Above(rng As Range)
Application.Volatile
If rng.Cells.Count = 1 Then
If rng.Row 1 Then Sum3Above = rng.Offset(-1, 0)
If rng.Row 2 Then Sum3Above = Sum3Above + rng.Offset(-2, 0)
If rng.Row 3 Then Sum3Above = Sum3Above + rng.Offset(-3, 0)
End If
End Function


And call like H12: =Sum3Above(H12)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"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

Name - relative reference doesn't calculate
 

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

Name - relative reference doesn't calculate
 

A few keystrokes shorter:

=SUM(Name)+NOW()*0

--
Biff
Microsoft Excel MVP


"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

Name - relative reference doesn't calculate
 

But the whole problem with it is he wanted a generic function to sum say 3
cells above, so if you have to include a superset of that range, it ceases
to be generic.

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

Name - relative reference doesn't calculate
 

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

Name - relative reference doesn't calculate
 

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

Name - relative reference doesn't calculate
 

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

Name - relative reference doesn't calculate
 

Biff,

the trick seems to be to get this dammend formula calculated when the
dependent cells are changed.
Some post earlier I have mentioned that I have found out that the
calculation behaviour seems to depend
on whether Excel recognizes dependents or not.

Example 1: (Excel doesn't calculate automatically, only by pressing
"Ctrl+Alt+F9:")
Formula in cell "F1" is "=SumEveryThirdValue" and "SumEveryThirdValue"
is defined as a
named formula "=SUM(!A1,!C1,!E1)".
When you check the dependecies with the detective you get no traces on
the sheet.

Example 2: (Excel calculates automatically)
Formula in cell "F1" is "=SUM(RangeSumEveryThirdValue)" and
"RangeSumEveryThirdValue" is defined as a named range "=!A1,!C1,!E1".
When you check the dependecies with the detective you get traces on
the sheet.

So the difference between Example 1 and 2 is that in Ex 1 uses a named
formula and
Ex 2 I uses a named range, which is calculated on the worksheet and
has dependent cells
(the named range).

Question is: is it logic that Example 2 works and what about
reliabilty?

Regards
Werner

Name - relative reference doesn't calculate
 

Werner Rohrmoser wrote...
I use a mane with a relative reference to sum 3 cells to the left
or in another case the 3rd, 6th, 9th and 12th cell to the left.
The syntax for the name is "!A1:A3" when my pointer is in A4,
formula is "=SUM(Name). I wrote it without the sheetname, because
I'd like to use it on every sheet I have in the book.
Now, when I change values in the precedent cells nothing happens
until I force a complete recalculation, it doesn't calculate
automatically, also F9 doesn't force the formula to calculate.
Any ideas or workarrounds, especially for "3rd, 6th, 9th and 12th"
case.
....

If the purpose of using such names is simplicity and uniformity of
formulas in adjacent cells, the better approach would be to use
worksheet-level names. For example, in worksheet A with cell A4 active
define the name A!name (predecing the name with the worksheet name
makes it a worksheet-level name rather than a workbook-level name)
referring to A!A1:A3. Enter the formula =SUM(name) in A4 and it
returns the sum of the values in A1:A3 in worksheet A.

Then copy A!A4 and paste it into B!A4, and that formula will return
the sum of the values in A1:A3 in worksheet B *AND* it will have
created the worksheet-level name B!name in worksheet B defined in the
same way that A!name is defined in worksheet A.

The potential disadvantage is that if you want to redefine such names,
you must redefine each one on every worksheet. You could use a macro
in a different workbook to iterate through all sheets in your main
workbook making the same changes to the relevant worksheet-level names.

Name - relative reference doesn't calculate
 

Harlan,

I don't know whether you've red posting 17 in this thread, where I
have described my experience
with named relative ranges and named formulas with relative ranges.
I'm not sure whether Example 2 works in every case.

Anyway, I have the feeling based on the discussion in this thread that
it's better to avoid
named ranges like "=!A1,!A3,!A5" because Excel seems to have a problem
with this design (do you have the same experience?)
Using worksheet level names to get reliable recalculation along with
the benefits of simplicity and uniformity should be my choice.

Regards
Werner

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

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

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

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

Name - relative reference doesn't calculate
 

Hi Charles,

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

regards
Werner

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.