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?
Formula moves Cell reference when cells are moved
Re: Formula moves Cell reference when cells are moved
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.
Re: Formula moves Cell reference when cells are moved
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?
Re: Formula moves Cell reference when cells are moved
Maybe the attached GIF sheds some light.
- Attachments
-
- In.xlsx
- (6.03 KiB) Downloaded 212 times
Re: Formula moves Cell reference when cells are moved
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.
Re: Formula moves Cell reference when cells are moved
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)
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)
- Attachments
-
- balance2.xlsx
- (9.81 KiB) Downloaded 229 times