13

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.

mat
  • 936

5 Answers5

19

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.

Daniel Möller
  • 361
7

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.

ruggb
  • 358
  • This works in Libreoffice, too (if only for the first time that I paste the cells). – mat Aug 07 '18 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 08 '18 at 20:54
5

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.

xCovelus
  • 153
  • 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 '20 at 15:13
  • What does "paste the original" mean? Paste in the same place where it was before being cut? – Daniel Möller Mar 10 '21 at 21:30
  • 1
    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) – David Tonhofer Dec 16 '21 at 12:17
4

I think this solution is easier and faster than previous:

  1. Select all cells to be copied
  2. Apply F4 as many times needed to make formulas absolute (adding $ in all sheets/columns/rows)
  3. Copy (Ctrl + C)
  4. Paste (Ctrl + V)
  5. (Optional) Apply F4 to the source and destination cells to make them relative again
DarkDiamond
  • 1,897
lourencosm
  • 41
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.

das Keks
  • 407
  • 1
  • 5
  • 16