inserting a row WITH any needed formulas

[Lee Bowman]

I made a check register sheet, with a formula placed in the 'balance'
column that combines entries in the 'debit' or 'credit' columns to the
running total in the cell above it for its new value. This, of
course, is a popular spread sheet application (new for me, though).

I generated the formulas in the 'balance' column by copying, so that
relative cell addresses created going down the column.

I ran into a problem when I tried to insert a new row in the sheet
where an entry had been omitted. The new row had no formula in it. I
had to unprotect the sheet, then copy the 'balance' formula from the
cell above it. (I actually had to copy the formula into two cells in
the 'balance' column, since one of the relative addresses was wrong
due to the row insertion - see below)

Before the row insertion:

E F G
DEBIT CREDIT BALANCE
5 G4-E5+F5
6 G5-E6+F6
7 G6-E7+F7

After inserting a row between 5 & 6:

E F G
DEBIT CREDIT BALANCE
5 G4-E5+F5
6 (newly inserted row)
7 G5-E7+F7
8 G7-E8+F8

See what happened? The old row 6 now becomes row 7, but the G5 value
didn't change to G6, (although the E6 & F6 values did increment to E7
& F7). I must now copy a formula into the G cell of the inserted row
(row 6), *AND* I must copy over the formula in G7 to make it correct
(or manually change the G5 value to G6).

Then, I must re-protect the sheet (actually only what I had selected
for protection, namely the formulas in the G column at a minimum.

Question:

Is there a modality where inserting new lines update the formulas
correctly without your having to do it manually? Seems like a
desirable feature!

I have Excel 2000. Thanks in advance!

Lee Bowman

 

[Guest]

Lee
I would write a macro as follows for inserting new row which enters formula and updates formulas below insert.I do not know if this is correct way but it works. I have also included macro for deleting unwanted row. You must be in row where you want insert/del to be when activating macro . The macro goes in modules. if you are new to macros go to tools macro visual basics editor then insert module copy these in and try
Hope this is of some use.
Sub Macroinsertrow()
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 6).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Sub Macrodeleterow()
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-1, 6).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End sub

 

[Guest]

Le
forgot about protection need to add these lines to macr
ActiveSheet.Unprotect 'at beginnin
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 'at en
Tina

 

[Lee Bowman]

Lee

forgot about protection need to add these lines to macro
ActiveSheet.Unprotect 'at beginning
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 'at end
Tina

Thanks, Tina. I will implement the macro.

Lee