In Calc: cut and paste without modifying unrelated cells

[Patchwork]

Hello, ive been trying to figure out how to do something and am having trouble figuring out how to do it.

my situation is as follows:
i have a spreadsheet with a set of columns, (say a-d) that contain just plain values as data, and a set of columns ( say e-j) that have formulas based on the data columns and themselves
what i wish to do is be able to cut and paste blocks of cells in the a-d columns and move them around. but this doesnt seem to be possible without this action modifying the formulas.

i assume what i want to do is actually a different hotkey than cut or paste but i havent been able to figure out what it is.

this is an example of what i did and what happened:
i selected the cells a2:d5
i hit control + x to cut them
i clicked on the cell a8
i hit control + v to paste the cells into a8:d11
i noticed that the cells in columns e-j that used to reference a2 now reference a8, which is undesirable to me

what im hoping to find is some sort of cut or paste without sideffects function that i can just bind the shortcut to instead but i dont know what this function is called in reality.

[mikekaganski]

https://help.libreoffice.org/latest/en-US/text/scalc/guide/relativ_absolut_ref.html?DbPAR=CALC

[Patchwork]

i feel like im not being understood? so i kinda want to offer a second example just incase.
with a new spreadsheet, if i put in the values 1,2,3,4 into the cells a1:d1. and the formula sum(a1:d1) into e1.
and then i copy that formula into e2, so it has the formula sum(a2:d2).
when i cut a1:d1 and paste it into a2:d2 then the formula in e1 changes to sum(a2:d2), which is what i want to avoid happening. because i dont need the formula in e1 to equal to e2.

if i instead make e1 be sum($a$1:$d$1), then after cutting and pasting a1:d1 into a2:d2 then the formula changes in the same way to sum($a$2:$d$2). its not an issue of relative refs to cells

[Wanderer]

Without testing, but from memory

@Patchwork

i hit control + x to cut them
…
i noticed that the cells in columns e-j that used to reference a2 now reference a8, which is undesirable to me

As you used cut Calc interprets this as “move this” and adjusts references.
.
To avoid this: Use copy ( ctrl +c ), then delete ( del), then paste at the desired place. As this inserts a copy no references should be changed.

[mikekaganski]

@Patchwork

i feel like im not being understood?

I see you already saw your mistake, and know now that all spreadsheet software works that way. But just to clarify things.

In all spreadsheets, there are relative and absolute references. And simple =A1 in a formula in B1 is not what you thought it was: it is not a "get data from cell A1", but “get data from the cell in the same row, one column left”. You see it “in disguise”, but that’s the relative reference; and when you copy and paste it elsewhere, it is not changed at all, and keeps telling “get data from the cell in the same row, one column left”. But when you look at it, the “cell in the same row, one column left” is shown to you in a convenient form, which depends on where the formula now is.

There is an absolute reference - like $A$1, meaning exactly "cell A1"; or mixed references like $A1 - meaning “column A, row relative”.

There is also a different notation R1C1, which is much better for understanding of the relative / absolute references, but - as @sokol92 rightfully mentioned elsewhere - you could have hard time guessing where a reference like R[240]C[132] points to.

Anyway: I hope you see now, that you are being understood correctly.

[Patchwork]

@Wanderer

To avoid this: Use copy ( ctrl +c ), then delete ( del), then paste at the desired place. As this inserts a copy no references should be changed.

this does do the exact thing i wanted, do you know if there is a nice way to say put this into a macro and have a hotkey assigned to this? that would solve my problem perfectly

[Patchwork]

@mikekaganski

Anyway: I hope you see now, that you are being understood correctly.

ive thinking about how to respond to this because i think its honestly harmful to the question im asking because its trying to derail the conversation to a topic ive tried to show explicitly isnt related to my problem, and im not sure how i could have explained what i wanted to achieve and at what steps in the process something i didnt want to have happen happened to hopefully sidestep issues i have in communicating my issue.

your asserting that something im explicitly stating isnt happening, and is explicitly not happening in my examples (copying formulas) is infact happening when the cells im copying contain nothing but data( raw numbers and text) and that in this series of actions formulas not within the selection of cells im moving are changing which im also marking as undesirable.

in this scenario whether references are absolute or relative has no bearing on this and im kind of at a loss where i should have worded things differently since i believe this is an issue on my end. but please dont assert your understanding me while directly contradicting me repeatedly in the same message.

[mikekaganski]

You are correct, and I realize that now, after re-reading your previous explanation (I needed to do that twice earlier).

tdf#167188

How do I stop a cell from following a cell that is cut/pasted?

If you cut and paste a cell, cell formula expressions that reference that cell are adjusted to point to the new position. That’s normal and expected spreadsheet behaviour. If you want to always reference the cell right and below (here referencing B2 from A1) without following cut&paste then use a named expression in which you defined a relative reference, e.g. on cell A1 define name rightdown as B2 and then use the name in the formula =rightdown

No option built in as of now, to avoid that. Only workarounds, as copy-delete-paste suggested above.

[PaulHam]

I assume that formulas in your cells in columns e-j are using “Relative References” and will look something like A2+B2. When you copy a such formulas you’ll experience the error you mentioned.

The best way to fix this is to us “Absolute References”. Where a dollar sign is placed before each letter and number in an absolute reference, for example, =$A$2:$B$2. When you copy and paste a formula with absolute references, you will always get the same value.

It is easy to convert between the relative and absolute references in LibreOffice in formula, is to select the cell and press F4

There is more information on changing references on this LO help page
https://help.libreoffice.org/latest/en-US/text/scalc/guide/relativ_absolut_ref.html

[Patchwork]

im not copying any of the formulas, im cutting and pasting cells in the colums a-d and that is changing the formulas in e-j. thats not how any other spreadsheet software works as far as im aware.

if i was copying the formulas id agree with this solution

[karolus]

@Patchwork

thats not how any other spreadsheet software works as far as im aware.

FALSE! any other spreadsheet software behaviour is very close to Calc, regarding how formula-references »follows« their source on cut and paste [parts? of] source !!

so please stop complaining and provide your real-world example.ods, to give others a chance to solve the issue

[Patchwork]

i guess you got me, aside from the previous software i was using which lacked a function i needed this seems to be pretty consistently how this is handled now… i still have the functionality im chasing that id like to be able to have, and am willing to change shortcut bindings for myself so it behaves the way i want if thats possible.

i had to go back and try other software and your absolutely correct. my bad

[Patchwork]

Sub MyCut
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())
End Sub

i ended up recording this macro based on what Wanderer had said and then binding this to cut’s hotkey to make it act the way i thought it would work, thank you all for the help!