I keep my checkbook registry in Excel. When I insert a new row, the new row
is blank, and then I manually copy the balance formula into the new row and
the one below. Is there an easier way to preserve a pattern of formulas when
inserting a row?

Here is an algorithm to preserve formulas when inserting a new row:
1. literally copy formulas of the selected row into the new row.
2. for all formulas in rows immediately above the new row, if formula
references the row immediately below the new row, the reference changes to
the new row.
3. for all formulas in rows immediately below the new row, if formula
references the row immediately above the new row, the reference changes to
the new row.

When inserting a new row, only apply the algorithm to row references that
are prefixed with €œ^€� (similar to €œ$€� notation for absolute cell references).


If multiple rows are inserted, apply the algorithm iteratively.

Similarly for inserting new rows.

Does Excel have such functionality?

Thank you.

You can copy the last row of data and then insert copied cells. then put new
data into of new last row of data. The formulas will automatically update

I highlight the last row of data and then right click the row and insert
copy cells above the present row.

"wolfv" wrote:

I keep my checkbook registry in Excel. When I insert a new row, the new row
is blank, and then I manually copy the balance formula into the new row and
the one below. Is there an easier way to preserve a pattern of formulas when
inserting a row?

Here is an algorithm to preserve formulas when inserting a new row:
1. literally copy formulas of the selected row into the new row.
2. for all formulas in rows immediately above the new row, if formula
references the row immediately below the new row, the reference changes to
the new row.
3. for all formulas in rows immediately below the new row, if formula
references the row immediately above the new row, the reference changes to
the new row.

When inserting a new row, only apply the algorithm to row references that
are prefixed with €œ^€� (similar to €œ$€� notation for absolute cell references).


If multiple rows are inserted, apply the algorithm iteratively.

Similarly for inserting new rows.

Does Excel have such functionality?

Thank you.

Joel,

Thank you for the quick response. Your method works for inserting a new row
to the end of a column of formulas.

I am inserting a new row into the middle of a column, so the row immediately
below the new row needs to change its reference to the new row.


"Joel" wrote:

You can copy the last row of data and then insert copied cells. then put new
data into of new last row of data. The formulas will automatically update

I highlight the last row of data and then right click the row and insert
copy cells above the present row.

You want the inserted-in-the-middle row to pick up the formula from above and
all rows below to update their references.

See David McRitchie's site for "InsertRows"

http://www.mvps.org/dmcritchie/excel/insrtrow.htm


Gord Dibben MS Excel MVP

On Sun, 3 Feb 2008 11:45:00 -0800, wolfv
wrote:

Joel,

Thank you for the quick response. Your method works for inserting a new row
to the end of a column of formulas.

I am inserting a new row into the middle of a column, so the row immediately
below the new row needs to change it’s reference to the new row.


"Joel" wrote:

You can copy the last row of data and then insert copied cells. then put new
data into of new last row of data. The formulas will automatically update

I highlight the last row of data and then right click the row and insert
copy cells above the present row.