Formula Reference when inserting a Insert Row in Table

[SteveT]

Hi All,


I have a chart that is based off of a table. In the table i have formulas that reference the row above to determine the starting position for this series. However, I would like the Users to have the ability to insert a row so that they can insert a series anywhere they want. I was going to use a table so that the formulas would automatically populate with the inserted row. However, when a user inserts a row, the formulas that reference the above row in the table stay more absolute then relative.


Here is a what happens:


Original Table

Header (A1) Formulas (B1)

Item1 =IF(ISNUMBER(B1),B1+10/5,1) = value of 1

Item2 =IF(ISNUMBER(B2),B2+10/5,1) = value of 3

Item3 =IF(ISNUMBER(B3),B3+10/5,1) = value of 5


Then when i insert a row i get:

Header (A1) Formulas (B1)

Item1 =IF(ISNUMBER(B1),B1+10/5,1) = value of 1

Item2 =IF(ISNUMBER(B2),B2+10/5,1) = value of 3

New Row =IF(ISNUMBER(B3),B3+10/5,1) = value of 5

Item3 =IF(ISNUMBER(B3),B3+10/5,1) = value of 5


As you can see the Item 3 formula kept its original reference and the New Row references the same row. I had expected that the ITEM 3 would have changed its reference to B4.


Any thoughts?

 

[Luke M]

The reason the formula didn't change as expected is because the output cell (the one with formula) moved, not the input cell (B3). The XL formula isn't keeping track of cell location by saying "1 cell above", it's thinking "the cell in row 3".


Now, to rewrite the formula to say the former, we can use the OFFSET function (which thinks in exactly those terms) and have the reference cell be the cell containing the formula (so if formula moves, the OFFSET moves).


In cell B2

=IF(ISNUMBER(OFFSET(B2,-1,0)),OFFSET(B2,-1,0)+2,1)


Note that I simplified the one operation of 10/5 to simply 2, as it appears to be the same in all your formulas.

 

[SteveT]

Thanks Luke. Works like a charm.


I hadn't thought about offset for this issue. Much appreciated!