Hi There,

I would like to make some Sub-totals (DataSubtotals) and replace the
resulting formulas
like eg: =SUBTOTAL(9;G2:G29)
with formula: =SUBTOTAL(9;G2:NextUp)
to make sure that when inserting a new row I sum properly!

As by Charly Kid: http://www.exceluser.com/solutions/errors_sums.htm

Where NextUp is a Defined Name created (with G29 being the active cell)
as: !G28
At first it seems a practical technique!

There is though a serious drawback to the use of this kind of Usage:
Discussion on:
http://www.dicks-blog.com/archives/2...defined-names/

Jan-Karel Pieterse:
Quote:
"If you use names like that, as long as Excel itself is firing the
recalculation of the worksheet, all is well.
BUT.
As soon as VBA fires a recalc (e.g. Application.Calculate)
Any cell that uses this global sheet-independent name will get the
value of cell A1 ON THE ACTIVE SHEET!
Unquote (Refering to the name: =!$A$1)

Stephen Bullen:
Quote:
"DONT DO THAT!!
Its even worse than Jan Karel suggests. If, for example, you have
calculation set to automatic, you can trigger a recalc in VBA by
setting a cells value in any worksheet. If you do, the €˜global
name will refer to the worksheet in which the recalc was triggered.
'
Unquote

These Excel-lencies strongly disapprove the usage of this kind of
Global names!
Though suggest the following:

JKP:
"Better to use =INDIRECT(€�A1€³), which has the desired effect too,
without the VBA recalc bug.
Only drawback is that the reference is fixed, ..
''
Charles Williams:
Quote:
"You can create global relative names using INDIRECT with R1C1 mode
reference, but personally I would prefer not to use such a
construction!
=INDIRECT(R[-1]C1,False)
"
Unqoute

IMO, to by-pass the problem of in-correct calculation when forcing a
VBA-recalc my NextUp name should be as described by Charles! Though it
seems he is not fond of his own "construction".

Why?
What are the drawbacks on this name?


Thanks for your insights!
Sige