5

I have a formula in Libreoffice Calc which I would like to move (Cut and Paste) to a different cell. I would like the formula to exactly stay the same without the cells in it being adjusted by the move. I cannot use static reference (using the $ sign) since on a different occasion I need the dynamic reference behaviour.

The only solution I have found so far, is manually copying and pasting the text of the formula instead of the cell itself. But this only works for a single cell and not for multiple ones.

4 Answers 4

6

when I have done a CUT & paste in Excel in the past, it transfers as is. The copy/paste will change cell references. I have not used LibreOffice.

2
  • This works in Libreoffice, too (if only for the first time that I paste the cells).
    – mat
    Aug 7, 2018 at 15:45
  • I can understand why that may happen, but I haven't tried it. A work around might be to copy/paste as many as u need in a remote section, then cut/paste each one. to the desired location
    – ruggb
    Aug 8, 2018 at 20:54
12

Trick:

  • Cut (ctrl + X)
  • Undo (ctrl + Z)
  • Paste (ctrl + V)

Since "cut-paste" works, but not "copy-paste", you can cut-paste and use the undo not to erase the old values.

0
2

Easier, easier: I tested Daniel Möller answer, but it failed (in MacOS at least), although it opened me the way to find the solution: Cut and paste, and then paste the original as well. That fixed it, no need for macros or complex stuff.

3
  • 1
    Daniel Möller's answer worked for me in Linux but I guess yours is more robust then, as it works for me in Linux, too.
    – Esmu Igors
    Dec 30, 2020 at 15:13
  • What does "paste the original" mean? Paste in the same place where it was before being cut? Mar 10, 2021 at 21:30
  • In LibreOffice 7.0.6.2 on Linux: CUT (CTRL-X), then PASTE (CTRL-V) to wherever it shall go, then PASTE (CTRL-V) again to where it originally was. If you do it in the order CUT, then PASTE to where it originally was, then PASTE to wherever it shall go, things don't work out as the second PASTE updates the formula's references (weird! unexpected!). This also works for a range of cells (as expected) Dec 16, 2021 at 12:17
1

This also annoyed me a lot so I found a workaround solution for this:

First create a Macro which simulates a cut by first copying and then clearing the cell:

Tools -> Macros -> Edit Macros -> Add the following to a module (e.g. Module1)

Macro

REM  *****  BASIC  *****

sub CutAndPaste

dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

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

end sub

Then assign the macro to Ctrl + X

Tools -> Customize -> search and select Ctrl+X in Shortcut Keys -> select LibreOffice Macros / My Macros / Standard / Module1 in Category -> select CutAndPaste in Functions -> press Modify on the right upper side to assign the macro to the shortcut.

assign macro to shortcut

You can now Ctrl + X, Ctrl + V to cut and paste without changing the references of other cells poiting to the cut cell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.