Formula moves Cell reference when cells are moved

Post Reply
Hexus5325
Posts: 3
Joined: Sat Jun 17, 2023 3:54 am

Formula moves Cell reference when cells are moved

Post by 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?
Attachments
Screenshot 2023-06-16 221041.png
Screenshot 2023-06-16 220532.png
SuperTech
SoftMaker Team
SoftMaker Team
Posts: 3172
Joined: Wed Mar 11, 2020 5:31 pm

Re: Formula moves Cell reference when cells are moved

Post by 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
Posts: 3
Joined: Sat Jun 17, 2023 3:54 am

Re: Formula moves Cell reference when cells are moved

Post by 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
SoftMaker Team
SoftMaker Team
Posts: 1233
Joined: Wed Jun 05, 2019 12:02 pm

Re: Formula moves Cell reference when cells are moved

Post by miguel-c »

Maybe the attached GIF sheds some light.
balance.gif
Attachments
In.xlsx
(6.03 KiB) Downloaded 212 times
Hexus5325
Posts: 3
Joined: Sat Jun 17, 2023 3:54 am

Re: Formula moves Cell reference when cells are moved

Post by Hexus5325 »

miguel-c wrote: Wed Jun 21, 2023 3:47 pm 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
SoftMaker Team
SoftMaker Team
Posts: 1233
Joined: Wed Jun 05, 2019 12:02 pm

Re: Formula moves Cell reference when cells are moved

Post by 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)
Attachments
balance2.xlsx
(9.81 KiB) Downloaded 229 times
Post Reply

Return to “PlanMaker 2021 for Windows”