Sum function with Insert row ???

[goepf]

I have the following problem:

example (see also attachment):
Cell E4 has a sum formula "=SUM(E5:E5)" and in E5 a have a value. An
the same for several columns

I would like to insert a row (between E4 and E5 or after E5) and hav
the SUM formula expanding the range to "=SUM(E5:E6)" with VBA.

This works fine if I already have more than one row in my formula (se
attachment) but if the area only has 1 row at the beginnig, it doesn'
expand the range automatically....

Please help!?

Attachment filename: insertexpl.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46838

 

[Trevor Shuttleworth]

One way:

=SUM(OFFSET(E4,1,):OFFSET(E6,-1,)) and drag across

Regards

Trevor

 

[Bob Phillips]

How about

=SUM(OFFSET(E4,1,0):E5)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[goepf]

I think my question was not very clear...

I have already a VBA (that is linked to a button) to insert a ro
(because the sheet is protected for the enduser).

And this VBA is not working properly (see code). I don't want to cop
paste new formulas every time...

Thanks for help again.

Sub InsertRowDay()
'
' InsertRow at active cell position
' Macro written 10/27/2003 by LANZGOE
'

Dim row As Integer
Dim Cell As String

row = ActiveCell.row
Cell = ActiveCell.Address

'Range("A" & Row).Select

If Selection.Locked = True Then
MsgBox ("You can't insert a row here. Place cursor in yellow cel
(not protected)!")
Exit Sub
ElseIf Selection.Offset(-1, 0).Locked = True Then
MsgBox ("Common Diane, You can't insert a row here!")
Exit Sub
End If

'Application.ScreenUpdating = False
ActiveSheet.Unprotect

Rows(row & ":" & row).Select
Selection.Copy
'Range("A" & row + 1).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("A" & row + 1).Select
Selection.ClearContents
Range("E" & row + 1 & ":K" & row + 1).Select
Selection.ClearContents
Range("A" & row + 1).Select

ActiveSheet.Protect DrawingObjects:=False, Contents:=True
Scenarios:=True

Application.ScreenUpdating = True

End Su

 

[David McRitchie]

The whole purpose of the OFFSET is to only directly reference
the cells relative to the row the formula is on. So that rows can
be inserted/deleted without having the change parts of a formula
manually.

For a row of totals:
E5: =SUM(E$2:OFFSET(E5,-1,0)) [--instead of =SUM(E2:E4) ]
copy across with the fill handle, so that
F5: =SUM(F$2:OFFSET(F5,-1,0))

For a balance column as on my insrtrow.htm page
F5: =OFFSET(F5,-1,0) + D5 - E5 [--instead of =F4+D5-E5]
fill down with the fill handle, so that
F6: =OFFSET(F6,-1,0) + D6 - E6

More information on
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

=SUM(OFFSET(E4,1,):OFFSET(E6,-1,)) and drag across

=