Sub-Totalling and Adjusting SUM formulae

[Tim Childs]

Hi

I want to add a new line programmatically to a table and there are a
number of <tables> (separated and below each other) of data on a
spreadsheet (each of which may need a line inserting for more data).
The main problem is that I need to adjust the formula (a
SUM)immediately below the table to include the inserted line. This is
easy for including the new last line but the one I am having trouble
with is the first line of the table - the table can contain a variable
number of lines - what is the easiest way round this (One solution I
have in mind is to put this first row at the design stage in a
rangename so it updates on its own as rows are added etc)

I don't want to use the eqivalent of just xlup as that assumes that
the header is always one row deep etc

THANKS

Tim

PS I have searched on Goog but did not find anything specific

 

[Dave Peterson]

You could just adjust the formula:

With numbers in A1:A10 and the sum formula in A11:
=SUM(A1:OFFSET(A11,-1,0))

David McRitchie has some notes at:
http://www.mvps.org/dmcritchie/excel/offset.htm

 

[Tim Childs]

David

Many thanks for that tip - it is really neat. How come I have been
using Excel for so long and never seen it before?

Tim

 

[Dave Peterson]

Not enough time reading David McRitchie's site?????

Spending too much time with the kids?????

 

[Tim Childs]

Hi

you are right on the first count, on the second even the kids quote to
me that nobody regrets on their deathbed that they didn't spend more
at/in the office.

relax!

Tim

 

[Dave Peterson]

(I was joking. And I bet you were, too. Well, about the relax part!)