Autofilling formulas when I insert a row

[myronschroner]

Hi,

I've got a rather complex Excel 2007 spreadsheet that others are going
to be editing, and when they insert rows I would like to have the
columns with formulas in them fill down to the new row.

I know there's a setting in Options to Extend Date Range Formats and
Formulas, but it doesn't seem to work when inserting a row in the
middle of a group. And I don't want to require them to put it at the
bottom and then manually move it to where it should go. And finally,
there's column that would work for sorting either.

I know it can be done if I use a macro, but they need to be able to
Undo, so I don't think macros are an option.

I know they could "copy" and "insert copied rows" or paste special,
but then the integrity of the spreadsheet would rely on them
remembering to do that rather than just insert a row.

Anyone have any ideas?

Thanks in advance!

Myron

 

[Roger Govier]

Hi

in XL2007 place cursor within your data area and choose Insert>Table>My
table has Headers
You can append data or insert rows in the table and the formulae will be
extended.
There will be a dropdown on each header which you can use to sort the whole
table by whichever dropdown you choose.

 

[myronschroner]

Awesome, that worked. Thanks!