inserting rows without adjusting formula relationships
 

I currently have a spread sheet that contains many rows of measurements. The
measurements are essentially all the same, taken over and over again for an
average.

I have the spreadsheet set up to calculate the required numbers
automatically, using some "IF" statements to accuont for different
situations. if the user only uses the spreadsheet as is it works well, but
one situation is when a measurement is taken twice, but counted as the same
(Averaged). To do this the user should be able to add a row and drag the
formulas down where needed.

When I do this, though, the referencing gets messed up. for example, if
there is a reference from cell A1 to cell A2, when I add a row between them
cell A1 changes the reference to A3, instead of leaving it as A2.

I could add $'s to preserve the cell numbers, but then that causes problems
when a user tries to add a new row for a brand new measurement. (maybe they
took more than normal and need more rows). same issue when deleting rows if
there are more rows than measurements taken.

So how do I have cell A1 not compensate for the new or deleted rows?

inserting rows without adjusting formula relationships
 

If you want the formula to always reference A2 no matter what, just
substitute INDIRECT("A2") to everywhere that A2 appears in your formula

-Simon


"Daiv" wrote:

I currently have a spread sheet that contains many rows of measurements. The
measurements are essentially all the same, taken over and over again for an
average.

I have the spreadsheet set up to calculate the required numbers
automatically, using some "IF" statements to accuont for different
situations. if the user only uses the spreadsheet as is it works well, but
one situation is when a measurement is taken twice, but counted as the same
(Averaged). To do this the user should be able to add a row and drag the
formulas down where needed.

When I do this, though, the referencing gets messed up. for example, if
there is a reference from cell A1 to cell A2, when I add a row between them
cell A1 changes the reference to A3, instead of leaving it as A2.

I could add $'s to preserve the cell numbers, but then that causes problems
when a user tries to add a new row for a brand new measurement. (maybe they
took more than normal and need more rows). same issue when deleting rows if
there are more rows than measurements taken.

So how do I have cell A1 not compensate for the new or deleted rows?