why don't my formulas adjust as they shift down rows?

[Todd]

I have a workbook with many page and I am inserting rows and copying formulas accross the workbooks (I am using a macro to do this). The formulas are not hard referenced but they do not adjust for the inserts. For example, =SUM('0110-000:0110-plate'!B49) should have become =SUM('0110-000:0110-plate'!B50) when it shifted down one row but it didn't. It remained unchanged.

What am I doing wrong?


Todd

 

[Bernie Deitrick]

Todd,

Inserting rows doesn't update formulas: copying and pasting them does. If
you want to be able to insert rows above a formula and have it update, you
need to use INDIRECT, as in

=SUM(INDIRECT("'0110-000:0110-plate'!B" & ROW())

assuming ROW() returns 49.

You also don't need to SUM a single cell - you can simply refence it, along
the lines of

='0110-000:0110-plate'!B49

HTH,
Bernie
MS Excel MVP

I have a workbook with many page and I am inserting rows and copying

formulas accross the workbooks (I am using a macro to do this). The
formulas are not hard referenced but they do not adjust for the inserts.
For example, =SUM('0110-000:0110-plate'!B49) should have become
=SUM('0110-000:0110-plate'!B50) when it shifted down one row but it didn't.
It remained unchanged.