Formula + Inserting Row Problem

Posted by Anurag Agarwal on September 21, 1999 11:31 PM

In a column I have the following formula that refrences the previous (top) row

IF(ISBLANK(A5),"",D4) for row 5

These extend from row 3 to row 20. If I now insert a row say between 4 and 5, I find that in row 6(was row 5) refrence D4 in the formula doesn't change, but for the other rows it changes.

How can this be solved

Thanks in Advance

Anurag

Posted by Ivan Moala on September 22, 1999 2:45 AM

Anurag
What has happened is correct ie. this should happen.
Inserting rows or columns should change the reference
to your original formula reference so that when making
a change like inserting, draging etc the reference within
your formulas remains intact, and the integrety of the results
is the same. Excel does this on purpose so that cell references
within formulas remain relative to each other.
If you want to insert a row say @ your example position
and maintain the formula then try;
1) select Insert
select Cells....
select "shift cells down"
only the active cell column range will be inserted.
2) Then in your range A3:A20, select A3
3) Click the cell handle "Bottom right corner"
4) Drag this down to you new range = range(A3:A21)
This will give you the new formulas adjusted to your
new cell insert.

regards

Ivan