insert row in list and formula does not update

[Christyr]

When I insert a new row inside my list, the formulas below do not
automatically update. They do not point to the new row numbers. It is as if
the formula thinks the cell reference is absolute. It is not.

any idea? is this a setting that I an turn off?
thanks.

 

[Bernard Liengme]

This is the way Excel was designed

In A10 I enter =F1 and copy this down to A15
Let column F has number 1,2,3,4....
So I see in A10:A15: the same series 1,2,3,4.....
Now I insert a row between A10 and A11
Now I see 1, (blank), 2,3,4....


If in A10 I use =INDIRECT("F"&ROW()-9) and copy. I still see 1,2,3,4
But now when I insert a new row I get 1, blank, 3, 4,

best wishes

 

[Nayab]

This is the way Excel was designed

In A10  I enter =F1 and copy this down to A15
Let column F has number 1,2,3,4....
So I see in A10:A15: the same series 1,2,3,4.....
Now I insert a row between A10 and A11
Now I see 1, (blank), 2,3,4....

If in A10 I use =INDIRECT("F"&ROW()-9) and copy. I still see 1,2,3,4
But now when I insert a new row I get 1, blank, 3, 4,

best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme







- Show quoted text -

I think the formulae get updated if you insert a row between the cells
that are referenced to in the formulae contained in the cell.
So, if you have A1:A10 pointing to N1:N10 and if you insert a cell
between A4 and A5 then A5 will change to A6 but will still point to
N5. But, if you insert a cell between N4 and N5 then N5 will become N6
and A5 will now point to N6 and not to N5