Formula moves Cell reference when cells are moved

[Hexus5325]

I have a formula that I would like to stay exactly how it is even when cells are moved. (It is the balance column of a budget sheet)

However it moves to the previously referenced cell when I move that cell. I'll explain better with the example.
Example:
F4: =C4+E4+F3
A4 is the date, C4 is Income, E4 is expense

They are sorted by date, so if I need to add a new expense between two others I need to move the data. So E4 gets moved down one to E5, and a new expense is placed in E4.

However, when I move the data from E4 to E5 the formula for F4 changes automatically. It changes to:
F4: =C5+E5+F3

I do not want this to happen, I want it to still reference the total on the date of A4. Otherwise it skews my data and doesn't include E4 expense in the balance! How do I lock the formula so it doesn't move when referenced cells are moved?

[SuperTech]

Thanks for your post. If you want to move any cell/range without any change in the formula, please press left mouse button and hold on that cell/ range and then move the cell/range holding the button.

[Hexus5325]

I'm very confused, that's what I'm doing. Like I left click the range/cells, then move it down. But then the formula in the cell NOT included in that range changes to reference the old cells in their new place. Does that make sense?

[miguel-c]

Maybe the attached GIF sheds some light.

[Hexus5325]

Maybe the attached GIF sheds some light.
balance.gif

It does not. I know how to copy the formula to span several cells. The problem is moving a cell that is referenced in the balance formula, results in the formula changing automatically to reference the same cell, even though that cell has moved.

[miguel-c]

Ah, got it.

If you press and hold the CTRL key while moving, it will produce the desired effect (it will copy instead of cut)

You can also use the indirect function as in the attached xlsx file.
=INDIRECT("C" & ROW())+INDIRECT("E" & ROW())+INDIRECT("F" & ROW()-1)