Problematic drag and drop (also cut and paste) behavior when using simple formulas. Video included

[Ozzuneoj]

I’ve had this problem happen multiple times and so far, I think I have been able to catch it every time.

Basically, when a cell contains a formula to add together multiple cells, if you drag and drop one of those cells to another location it will change the formula within the other cell in such a way that it will keep adding additional copies of a cell over and over. I have recorded a video of the simplest situation where this can occur.

https://drive.google.com/file/d/1GC3ak7Hrzo-8gCF--6SFmkcuzu78BLxw/view?usp=share_link

I have no idea if this is intended behavior, but it has been happening for a while. I can’t think of a single situation where you would want to have things work this way because it destroys the accuracy of the data presented on the screen. Moving a “1” from one cell within a formula to another cell within the formula should never change the result to “2”, regardless of the logic going on in the background.

I also don’t like the fact that dragging and dropping a single cell actually changes the formula within another cell without some kind of visual indicator on screen. I would much rather that it show me the colored highlights (like when I select the formula) of the formulas connecting to that cell if I am changing them, at least as an option. Alternatively, I would like to be able to set the default drag and drop behavior to ONLY affect the data of the cell, not the connected formulas of other cells… again, with the colored highlights popping up when the cells are being dragged.

Also, I just realized that cutting and pasting a cell has a similar problem. It leaves the “value” of the old cell in the result of the formula. So, if I have a 1 in a cell, cut and paste it to another cell within the formula, the total is now 2. Why would anyone want this?

Tested on:
LibreOffice Calc 7.4.3.2 x64 (and many previous versions)
Windows 10 21H2
Using existing files as well as a brand new blank .ods file as shown in the video.

[karolus]

may I ask…why not simply =SUM(B1:B8)

I would take it as expected behavior, ( the Formulareference »follows« the dragged and dropped »source« )

[Ozzuneoj]

Thank you for the tip! I’ll admit, I have never done much work with spreadsheets, so I was just doing things that way because that was what I had figured out. I see that doing a range (B1:B8) as you showed avoids the problem entirely, which is great! I will definitely be changing all of my formulas to reflect this and I feel a little dumb not realizing there was a difference, but I never really thought about doing it a different way since it just worked aside from the issues mentioned here.

I think I’m starting to understand the logic behind the behavior where dragging and dropping or cutting and pasting cells seems to make duplicates of the same entry within the formula. It seems massively problematic if you don’t realize it is happening though (either because of poor formula formatting, or other reasons). Because of that, I feel like some visual indicator of what is happening to formulas when cutting or dragging cells would be really beneficial for less experienced users.

Here is a situation… I think I’m getting how this works. I have =sum(B9,B10) in a different cell. If I cut cell B9 the formula changes to =sum(B10). If I then paste it into B10 (leaving B9 empty), the formula changes to =sum(B10,B10) because I have just pasted that reference into B10, and the existing one is apparently still there…? I do get what it’s doing, but it seems very illogical from a practical standpoint, because there is only one set of data within the cell, so why duplicate the formulas? It seems like it should overwrite any references to the cell that was replaced or pasted over, not add more to them. Is there a situation where this would be desired behavior?

Again, I’m new to all this. I’m thinking of this in terms of writing numbers on a chart to keep accurate data, not from a programmers standpoint. If I can understand this better, that will help me in the long run.

Thanks again for your time.

(Also I’m not sure what’s going on with the formatting here but my paragraph spacing seems to not be working? It is turning this post into a wall of text, but it looks fine in the preview.)