Formulas Affected by Insert Row

[MJS]

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

 

[David McRitchie]

You want to use
in row 6 =OFFSET(A5,-1,0) instead of =A5
see http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[Teethless mama]

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

 

[MJS]

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.

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

 

[David McRitchie]

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

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.

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

 

[MJS]

Thanks David. The macro in question worked.

When I said the extend option is turned on by default, that was based on the
online help I researched for Excel 2007. I don't know whether that statement
was specific to 2007 or not. In any event, I did have it on. And I did get
unreliable results which begs the question, what is it there for if you can't
count on the results? Very annoying.

Anyway, I also was testing the insert ensuring there were at least 5 rows
above my insert point with the formulas I wanted copied. And it didn't work.

As I said your macro worked. My next difficulty though is that I will have
to deliver this spreadsheet to others (some from other companies). I created
my own digital signature and attached it to the macro but I suspect I'd have
to deliver the signature file itself. Yes? Where are these files stored?
I've been searching help for indication of where they are stored and
searching my hard drive for a file that I assume is called the name of the
certificate I created. But no luck so far. Also, I had to save the file as
a macro enabled version (xlsm) and had to enable digitally signed macros. So
I assume that's the way to go. Yes?

M.

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

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.

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

 

[David McRitchie]

Sounds round about enough to work for Excel 2007,
I don't like any aspect of 2007, except when I was using it to work with
a years worth of data it would have been nice to have more than 256 columns then,
but most of the new stuff is so annoying, restrictive, hard to use, and outright annoying.
Trying to work without menus and my toolbar buttons is the pits. I at least know some keyboard
shortcuts, and have to do practically everything with context menus now..

 

[MJS]

So do you know what the signature files are called, where they are stored? I
have found absolutely no references anywhere in microsoft and no search of my
hard drive surfaced anything that might be a 'signature file'. Or maybe it's
attached to the workbook? I sort of doubt that.

M.