17

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
  • 1,046

6 Answers6

23

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
  • 401
9

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
  • 378
6

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
  • 163
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,919
  • 11
  • 15
  • 21
lourencosm
  • 41
2

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
  • 457
0

None of the existing answers worked for me when copying/cutting data from an imported CSV to a different sheet. I had to manually add empty CSV lines and columns to position the data where it would go in the other sheet. Then copy/paste worked.

calamari
  • 101