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

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

edit retag close merge delete

Sort by » oldest newest most voted

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

more

Summing up the two very helpful suggestions I got.

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

more

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:

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.

more