Re: Inserted Rows not re-calculating

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <klam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1998B4E2-4E74-422E-90E1-DFABA3441087@xxxxxxxxxxxxxxxx
> > 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
> > > > work*** 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 Calculate***, 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,
>
>
>
.

Quantcast