How do I arrange for the sum totals in the last line on a worksheet with
several columns of figures will automatically adjust itself when I enter an
additional line or lines of figures, so that I do not have to use the
totalling tool every time I make entries on the worksheet.

"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet with
several columns of figures will automatically adjust itself when I enter
an additional line or lines of figures, so that I do not have to use the
totalling tool every time I make entries on the worksheet.



Make sure that the range summed contains one or more extra rows - then just
insert your new rows before the end of the range....

Hi

If you have XL2003, you can use DataListCreate list which will
automatically extend formulae as you add more rows to the list, with a
toggle to switch on or off the Totals at the bottom of the list.

For XL2007, an improved version exists with InsertTable.

--
Regards

Roger Govier


"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet
with several columns of figures will automatically adjust itself when
I enter an additional line or lines of figures, so that I do not have
to use the totalling tool every time I make entries on the worksheet.

Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.


"Gordon" wrote in message
...
"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet with
several columns of figures will automatically adjust itself when I enter
an additional line or lines of figures, so that I do not have to use the
totalling tool every time I make entries on the worksheet.



Make sure that the range summed contains one or more extra rows - then
just insert your new rows before the end of the range....

"Zygy" wrote in message
...
Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.


If you sum (say) A1:A35, but your actual data is A1:A25 and you insert a row
at A26, then the sum should still work.

With, as an example, data in B2:B14 and this formula in B15

=SUM(B2:OFFSET(B15,-1,0))

If you select Row 15, (the one with the formula), or any row above it and
insert a new row the formula will adjust to accommodate the new row.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Zygy" wrote in message
...
Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.


"Gordon" wrote in message
...
"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet with
several columns of figures will automatically adjust itself when I enter
an additional line or lines of figures, so that I do not have to use the
totalling tool every time I make entries on the worksheet.



Make sure that the range summed contains one or more extra rows - then
just insert your new rows before the end of the range....

Zygy

Assuming your current data is in A1:A10

Enter this in A11

=SUM(A1:INDEX(A:A,ROW()-1))

As you insert rows above A11 it will include the new rows.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 13:42:56 +0100, "Zygy" wrote:

Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.


"Gordon" wrote in message
...
"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet with
several columns of figures will automatically adjust itself when I enter
an additional line or lines of figures, so that I do not have to use the
totalling tool every time I make entries on the worksheet.



Make sure that the range summed contains one or more extra rows - then
just insert your new rows before the end of the range....

Just to make it absolutely sure I have the formula right. My entries in
Col.C are up to line 79, so my formula to enter in Col.C line 80 should
read -

=SUM (C1:INDEX(C:C,ROW()-1)) Am I correct?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Zygy

Assuming your current data is in A1:A10

Enter this in A11

=SUM(A1:INDEX(A:A,ROW()-1))

As you insert rows above A11 it will include the new rows.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 13:42:56 +0100, "Zygy" wrote:

Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.


"Gordon" wrote in message
.. .
"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet
with
several columns of figures will automatically adjust itself when I
enter
an additional line or lines of figures, so that I do not have to use
the
totalling tool every time I make entries on the worksheet.



Make sure that the range summed contains one or more extra rows - then
just insert your new rows before the end of the range....

Yes, the formula would be entered into C80


BTW............get rid of the <space after =SUM


Gord

On Wed, 16 May 2007 13:02:09 +0100, "Zygy" wrote:

Just to make it absolutely sure I have the formula right. My entries in
Col.C are up to line 79, so my formula to enter in Col.C line 80 should
read -

=SUM (C1:INDEX(C:C,ROW()-1)) Am I correct?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Zygy

Assuming your current data is in A1:A10

Enter this in A11

=SUM(A1:INDEX(A:A,ROW()-1))

As you insert rows above A11 it will include the new rows.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 13:42:56 +0100, "Zygy" wrote:

Hi,

I tried it and it worked only when I included the additional lines in the
Sum but when I added another line after that your suggestion did not work.
So, what did I do wrong?
Incidentally this is on Office 2000.


"Gordon" wrote in message
. ..
"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet
with
several columns of figures will automatically adjust itself when I
enter
an additional line or lines of figures, so that I do not have to use
the
totalling tool every time I make entries on the worksheet.



Make sure that the range summed contains one or more extra rows - then
just insert your new rows before the end of the range....

Your formula works fine! Thank you and thank you also to all others who
contributed to my posting.
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Yes, the formula would be entered into C80


BTW............get rid of the <space after =SUM


Gord

On Wed, 16 May 2007 13:02:09 +0100, "Zygy" wrote:

Just to make it absolutely sure I have the formula right. My entries in
Col.C are up to line 79, so my formula to enter in Col.C line 80 should
read -

=SUM (C1:INDEX(C:C,ROW()-1)) Am I correct?

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Zygy

Assuming your current data is in A1:A10

Enter this in A11

=SUM(A1:INDEX(A:A,ROW()-1))

As you insert rows above A11 it will include the new rows.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 13:42:56 +0100, "Zygy" wrote:

Hi,

I tried it and it worked only when I included the additional lines in
the
Sum but when I added another line after that your suggestion did not
work.
So, what did I do wrong?
Incidentally this is on Office 2000.


"Gordon" wrote in message
.. .
"Zygy" wrote in message
...
How do I arrange for the sum totals in the last line on a worksheet
with
several columns of figures will automatically adjust itself when I
enter
an additional line or lines of figures, so that I do not have to use
the
totalling tool every time I make entries on the worksheet.



Make sure that the range summed contains one or more extra rows - then
just insert your new rows before the end of the range....