insert row and sum function AGAIN

  • Thread starter Thread starter goepf
  • Start date Start date
G

goepf

I already tried to find a solution for my problem with another post
sorry to come back but the answers were not solving my problem (o
maybe I'm too stupid...)

The attached file shows the problem:

I have a sum for values in the following rows. The code to insert a ne
row works fine but the sum function only expands to that new added ro
if there was already more than one row. (see example).

any idea (I don't think that the offset function is really th
solution...)

thank

Attachment filename: insertrow.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47064
 
This is the situation for which OFFSET was suggested.

F2: 11
F3: 23
F4: 18
F5: =SUM(F$2:OFFSET(F5,-1,0))
allows insertion of rows between current row 4 and 5.

Sine you are putting your totals above the detail and including a
blank row between the next group, I suppose you will have to include
that blank rown in your sum.

F2: =SUM(F3:F6) or even OFFSET(F2,1,0):F6)
F3: 11
F4: 23
F5: 18
F6: (entire row is empty)

Another possibility is the use of SUBSTOTALS.
http://www.mvps.org/dmcritchie/excel/sumdata.htm#subtotals

It would be a lot more appropriate if you kept to your original thread,
and posted in word all information needed to ask a question without
resorting to posting a reference to a file -- I also doubt that that file
will be kept for 20 years and I expect newsgroup postings to last at
least that long.
 
David,

I'm sorry that I didn't follow some of the board rules (I thougt tha
if there is a possibility to attach a file than this is ment to b
used...).

Your help brought me 1 step further but it is still not working:

if I use the following formula:

F1: =sum(F2:offset(F1,1,0))
F2: 3

then F1 = 3

if I insert a row after F2 with the following code:

Rows(2:2).Select
Selection.Copy
Selection.Insert Shift:=xlDown

the formula in F1 is updated "correctly" (F3:F2 instead of F2:F3) to:

F1: =sum(F3:offset(F1,1,0))
F2: 3
F3: 5

then F1 = 8

BUT:

if I delete then a row again (F3) with the following code:

Rows(3:3).Select
Selection.Delete

the formula is messed up because of the reference to F3...

Any further idea?

Thanks and sorry again for creating a new post (must be my Swis
nationality ;-
 
Is there anything else in that column that you have to avoid?

If no, how about just:

=SUM(F2:F9999)

(make that 9999 big enough to fit everything possible)
 
Is there anything else in that column that you have to avoid?

yes of course I have several similar totals in different rows...

that would have been way too easy. but thanks anyway
 
Reread my reply your total line is above your detail
your sum should be from the cell below your total line down to and including
the blank row before the detail for that total.

=
 
Back
Top