Forcing relative reference to update

[Reg]

If this has been answered before, I apologize: I don't even know how to describe it clearly enough to search for it.

I have a sheet with, say, two columns of numbers. One of these columns is summed to a total row [=SUM(D4:D15)]; the other column has the last value repeated on the total row [=E15] (so the total row always shows the total of the first column and the highest (or latest) value of the second column).

When I insert a new row above the total row and enter new values in these columns, the Sum formula updates itself and shows the new sum [=SUM(D4:D16)], but the reference to the latest value does not.

Is there a way to make the reference change its source as well (by adding one to the row, e.g. from =E15 to =E16)?

Thanks

Reg

[oldchippy]

Not sure whether I understand exactly where your totals are but, if you have your numbers in cells D4:D15 and your SUM in D17

=SUM(D4:D16) when you insert a new row above the total the SUM will update to include that row and move down to row D18

=SUM(D4:D17), if you want to display the last number in the list D16 in cell E18, then use the OFFSET function

=OFFSET(D18,-2,0) basically this say's tell me what's in the cell two rows above the total.

[Reg]

Thank you -- offset() is the command I have been looking for (and didn't know existed)

Reg

[oldchippy]

Glad it worked for you - thanks for the feedback