I use SUM(Column_Title) a lot as I often do not know how many rows the
worksheet will grow to. It works great but when I add in rows, and
double-click on the formula cell, the extra rows I added are not included in
the formula range (the colour-highlighted box).

My workaround is to double-click on the formula, then double-click the
colunm title (in Row 1). Then when I double-click the formula again, it
shows it formula range includes the rows I added.

This has been happening for a while, and the workaround is tiresome as there
are many columns in some of my worksheets (and I have to double-check to make
sure I didn't miss any). I have tried F9 and CalculateSheet, both have no
effect. Is there some way to get it to update automatically? (I thought
that was the point of it, rather than having to input a cell range.)

Excel 2000 in XP.
cheers,

I find that if I don't include the cell right above my sum cell, that
happens. If I include that cell, the additional rows I add are included in
the total and I don't have to adjust the formula.

"klam" wrote:

I use SUM(Column_Title) a lot as I often do not know how many rows the
worksheet will grow to. It works great but when I add in rows, and
double-click on the formula cell, the extra rows I added are not included in
the formula range (the colour-highlighted box).

My workaround is to double-click on the formula, then double-click the
colunm title (in Row 1). Then when I double-click the formula again, it
shows it formula range includes the rows I added.

This has been happening for a while, and the workaround is tiresome as there
are many columns in some of my worksheets (and I have to double-check to make
sure I didn't miss any). I have tried F9 and CalculateSheet, both have no
effect. Is there some way to get it to update automatically? (I thought
that was the point of it, rather than having to input a cell range.)

Excel 2000 in XP.
cheers,

Thx for the reply letto115.

Strange for mine, my Sum (row 57) includes row 56, and when I insert rows
they are not included in the formula.

I did a test file to see if I didn't include the row before, if newly
inserted rows would be included in the formula, and it did not work either.

Any other ideas?

cheers,

"letto115" wrote:

I find that if I don't include the cell right above my sum cell, that
happens. If I include that cell, the additional rows I add are included in
the total and I don't have to adjust the formula.

"klam" wrote:

I use SUM(Column_Title) a lot as I often do not know how many rows the
worksheet will grow to. It works great but when I add in rows, and
double-click on the formula cell, the extra rows I added are not included in
the formula range (the colour-highlighted box).

My workaround is to double-click on the formula, then double-click the
colunm title (in Row 1). Then when I double-click the formula again, it
shows it formula range includes the rows I added.

This has been happening for a while, and the workaround is tiresome as there
are many columns in some of my worksheets (and I have to double-check to make
sure I didn't miss any). I have tried F9 and CalculateSheet, both have no
effect. Is there some way to get it to update automatically? (I thought
that was the point of it, rather than having to input a cell range.)

Excel 2000 in XP.
cheers,

Your posting was too ambiguous before but the problem you state now
is what the previous response addressed.

What you want to do is change your formula instead of
D57: =SUMB(D2:D56)
use
D57: =SUM(D$2:OFFSET(D57,-1,0))
then you insert a row immediately above your total row
without resorting to maintaining an empty row immediately before
your totals. More information in
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"klam" wrote in message ...
Thx for the reply letto115.

Strange for mine, my Sum (row 57) includes row 56, and when I insert rows
they are not included in the formula.

I did a test file to see if I didn't include the row before, if newly
inserted rows would be included in the formula, and it did not work either.

Any other ideas?

cheers,

"letto115" wrote:

I find that if I don't include the cell right above my sum cell, that
happens. If I include that cell, the additional rows I add are included in
the total and I don't have to adjust the formula.

"klam" wrote:

I use SUM(Column_Title) a lot as I often do not know how many rows the
worksheet will grow to. It works great but when I add in rows, and
double-click on the formula cell, the extra rows I added are not included in
the formula range (the colour-highlighted box).

My workaround is to double-click on the formula, then double-click the
colunm title (in Row 1). Then when I double-click the formula again, it
shows it formula range includes the rows I added.

This has been happening for a while, and the workaround is tiresome as there
are many columns in some of my worksheets (and I have to double-check to make
sure I didn't miss any). I have tried F9 and CalculateSheet, both have no
effect. Is there some way to get it to update automatically? (I thought
that was the point of it, rather than having to input a cell range.)

Excel 2000 in XP.
cheers,

Wow, I didn't know it was so complicated! Thx a mint for your response
David; it took me a while to read thru your links. The offset solution
certainly works! And, it will be a well-used solution in my work.

letto15: Sorry I'm such an unsophisticated user that I didn't even
understand your reference to be the solution!

cheers all,

"David McRitchie" wrote:

Your posting was too ambiguous before but the problem you state now
is what the previous response addressed.

What you want to do is change your formula instead of
D57: =SUMB(D2:D56)
use
D57: =SUM(D$2:OFFSET(D57,-1,0))
then you insert a row immediately above your total row
without resorting to maintaining an empty row immediately before
your totals. More information in
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"klam" wrote in message ...
Thx for the reply letto115.

Strange for mine, my Sum (row 57) includes row 56, and when I insert rows
they are not included in the formula.

I did a test file to see if I didn't include the row before, if newly
inserted rows would be included in the formula, and it did not work either.

Any other ideas?

cheers,

"letto115" wrote:

I find that if I don't include the cell right above my sum cell, that
happens. If I include that cell, the additional rows I add are included in
the total and I don't have to adjust the formula.

"klam" wrote:

I use SUM(Column_Title) a lot as I often do not know how many rows the
worksheet will grow to. It works great but when I add in rows, and
double-click on the formula cell, the extra rows I added are not included in
the formula range (the colour-highlighted box).

My workaround is to double-click on the formula, then double-click the
colunm title (in Row 1). Then when I double-click the formula again, it
shows it formula range includes the rows I added.

This has been happening for a while, and the workaround is tiresome as there
are many columns in some of my worksheets (and I have to double-check to make
sure I didn't miss any). I have tried F9 and CalculateSheet, both have no
effect. Is there some way to get it to update automatically? (I thought
that was the point of it, rather than having to input a cell range.)

Excel 2000 in XP.
cheers,