Inserting rows into sheet that contains a formula

[rick]

I have a typical financial sheet where the columns sum (sum(a5:a15)) and the
rows sum (sum(a5:h5)). The summation columns are locked and the sheet
protected.
Is there a technique whereby I can have the user insert a row which will
keep the formats and row sum as well as changing the formula in the column
sums?

Thanks.

Rick

 

[OssieMac]

Hi Rick,

The following method is not foolproof but generally it works OK.
Insert a blank row between the bottom of data to be summed and the row with
the sum formulas.
Include the blank row in the sum formula.
Protect the blank row along with the formulas and whatever else to be locked
and protected.
Now when the user needs an extra row they cannot use the existing blank row
so must insert one above it.
Because the insertion is in the middle of the range used for the sum
formula, the sum formula automatically updates.
The Inserted row carries the formatting of the row above it.

 

[rick]

Thank you very much OssieMac.

Hi Rick,

The following method is not foolproof but generally it works OK.
Insert a blank row between the bottom of data to be summed and the row
with
the sum formulas.
Include the blank row in the sum formula.
Protect the blank row along with the formulas and whatever else to be
locked
and protected.
Now when the user needs an extra row they cannot use the existing blank
row
so must insert one above it.
Because the insertion is in the middle of the range used for the sum
formula, the sum formula automatically updates.
The Inserted row carries the formatting of the row above it.