[calc] Can I cut & paste the *contents* of multiple cells at once without affecting references and formats?

[barak]

If I triple-click a single cell I can cut and paste just the content of that cell, without affecting cell formatting and without messing up formulae which reference the cell I’m working on. Is there a way to do that for multiple cells at once? Maybe some sort of cut-contents-only keyboard shortcut?
More generally, is there a way to “freeze” cell formatting and formulae in place and then work on cell contents only?
Thanks, B

LibreOffice 4.2.2.1 en-us
Mac OS X 10.7.5 en-us

LibreOffice 3.5.7 en-us
Ubuntu 12.04

---

EDIT
While the question about cell formats still stands, turns out that on references I was partially wrong. Not being able to attach a file, I’ll try to reformulate the question as best as I can.
I have three columns: A (time), B (speaker name), C (speech duration). On each row except the first, time is obtained by adding cols A and C of the row above (eg. A2 = A1+C1 and so on). If I cut, say, the third speaker (B3:C3) and paste him/her elsewhere, all formulae in column A are unchanged. If I need to shift all speakers from (say) the third to the last down one row to make room for another speaker (cut B3:Clast and paste that one row down) then formulae starting with A5 downwards are “updated” (eg. A5=A4+C5 instead of A4+C4). This occurs only if I select from any given row to the last one. My question is, how can I prevent this behavior? I would like formulae to remain unchanged no matter what.

Sorry for the long and convoluted explanation. Thank you for your patience, B

[mariosv]

Usually if the formulas have the references set up properly using absolute/relative references, it’s possible to do it, pasting with: right-click Paste Only - formula.

edited 20150801

After more detail in the question.

It’s possible construct a formula that always refer a relative reference to their position:

=OFFSET(INDIRECT(CELL(“address”));-1;0)

always take the cell in the row before in the same column

=OFFSET($A$1;ROW()-2;COLUMN()-1)

does the same, but their limitation is to not move A1

=OFFSET(C$1;ROW()-2;0)

also works, but in this case the limitation is to not move C1

But it’s a heavy consuming function, maybe can delay a bit if you are going to use thousands.

[barak]

Thank you for your answer but I wasn’t asking about moving formulae. Sorry, my question wasn’t clear enough and my assumptions were partially wrong. I tried to reformulate it, please see above.

[PYS]

Hi

Starting from these data in Sheet1:

For inserting Z between a & b:

• Copy A3:C7, paste in another sheet (e.g. Sheet2) in A2
• Type Z and duration in Sheet1.B3 & C3
• Copy “saved” data in Sheet2, Paste in Sheet1.A4

Regards

[barak]

Summing up the two very helpful suggestions I got.

• Pierre-Yves’ answer: basically, use
  copy rather than cut. It works, and
  also helps maintaining cell format.
  As far as I can tell, there is no
  need to paste in another sheet, just
  keep the data in the clipboard and
  paste back at the new location.
• m.a.riosv’s answer: use indirect/absolute references. In my
  case, formula would be =
  (OFFSET(INDIRECT(CELL(“address”));-1;0))+(OFFSET(INDIRECT(CELL(“address”));-1;2))
  – looks awfully convoluted but works perfectly and is perhaps a more
  elegant solution.

I learned a lot. Thanks to both for your time, B