cell formula not updating right after insert row

[Ben]

Hello,

I have a formula in a column as follows - the details of what it does are for
the most part unimportant (this one is at row 50):
=IF(D50<>"",D50-SUM(G50:AA50), IF(C50<>"", E49 +C50-SUM(G50:AA50),""))

I frequently end up inserting rows and copying the above formula to the right
column for the new row. What happens though is that this formula and all those
following get messed up as so:
=IF(D51<>"",D51-SUM(G51:AA51), IF(C51<>"", E49 +C51-SUM(G51:AA51),""))

Everything got adjusted except for the reference to E49 which should have
become E50 (in the new row). I presume that it tries to keep references to
where they were before that this happens but it messes me up in this instance
as current rows need to know the immediately prior row and not earlier (like a
chain). Is there anything anyone can recommend to me regarding this?

It is actually a part of a spreadsheet that helps me plan the budget for my
home where I can either specify how much is in my account or how much I expect
to see deposited (in which case the amount is added to the prior effective
total - that's where the e49 came in). I can then specify different bills for
that time on the same row to see what or when I can afford to pay them.

Thanks,
Ben

 

[Guest]

Ben

When adding a new row select the entire row either by clicking the row number in the left most margin or picking any cell in the row and do a 'Shift-SpaceBar to highlight all the cells of that row in the worksheet. Then do a Ctl-C to copy, right-click on the mouse and Insert Copied Cells. You can then overwright the duplicate values, but this should automatically copy all your formulas and update the ranges without manual intervention.