Auto Extend is not on by default. And check out it behavior in...

XL2000: How Auto Extend List Behavior Works

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q231002
Three of the last five rows must have the formula, you don't have

enough rows if you have a header row and something for the formula

to work on you do not have enough rows the criteria to kick in with =A5

I would not put much reliance in Auto Extend if not always the same,

I keep the option turned on but I rely on the macro that I created to

copy formulas down from preceding row.

Insert a Row using a Macro to maintain formulas

http://www.mvps.org/dmcritchie/excel/insrtrow.htm
What the macro does is insert the number of rows requested downward

and copies the formulas into the new rows, by copying the entire row and

removing constants. Means that the formulas are adjusted. Which is what

you asked for, but the use of OFFSET instead of INDIRECT, I think, you

would find easier to work with. You main problem was the lack of sufficient

rows with he formula in them. My macro requires only the row that you

have selected (if multiple rows are selected, the row with the active cell).

And you are always asked how many rows you want to insert. If you have

a specific number of rows to insert below see the documentation, and change

the calling macro not the main macro.

--

HTH,

David McRitchie, Microsoft MVP -- Excel

My Excel Pages:

http://www.mvps.org/dmcritchie/excel/excel.htm
MJS said:

Thanks all. But this doesn't explain why, currently, no formulas are showing

up in those columns in the inserted row. Is there something else I'm missing

(an option/parameter) that ensure all formulas are copied to new rows?

M.

Teethless mama said:

In A6: =INDIRECT("A"&ROW()-1)

:

I'm not sure I'm posting this in the right place but here goes.

I'm using Excel 2007. Excel is "supposed" to default to copying formulas

into inserted rows by default (assuming previous rows have the same formula).

I've also ensured the option to Extend data range formats and formulas is

checked in [Windows Symbol]->Excel Options->Advanced.

My formulas are not being copied to the inserted row and one formula in the

row below where a relative reference to the row above is not adjusted

correctly (i.e. a simple "=A5" for row 6 on insert remains "=A5" rather than

adjusting to "=A6").

So two different problems but both occurring when I try to insert a row.

Any idea why this isn't working properly?

MJS