Ask Your Question
0

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

asked 2015-07-29 23:38:10 +0200

barak gravatar image

updated 2015-08-23 19:14:33 +0200

Alex Kemp gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2015-08-01 12:29:25 +0200

pierre-yves samyn gravatar image

Hi

Starting from these data in Sheet1:

screenshot

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

edit flag offensive delete link more
0

answered 2015-08-01 17:09:20 +0200

barak gravatar image

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

edit flag offensive delete link more
0

answered 2015-07-30 02:25:59 +0200

m.a.riosv gravatar image

updated 2015-08-01 10:57:22 +0200

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.

edit flag offensive delete link more

Comments

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.

barak gravatar imagebarak ( 2015-07-30 18:09:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-29 23:38:10 +0200

Seen: 1,878 times

Last updated: Aug 01 '15