In Calc: cut and paste without modifying unrelated cells

English
#1

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.

#2

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

#6

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

#8

Without testing, but from memory

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.

#3

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

#4

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

#5

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

#7

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

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.