Ask Your Question
0

How to NOT reference cells from another sheet when pasting into a new sheet

asked 2018-03-06 04:15:09 +0100

thomoz gravatar image

updated 2018-03-06 09:06:19 +0100

Jim K gravatar image

Hi

When I copy a cell formula from one sheet into another, it references the sheet it came from, which screws up my calculations.

E.g. copying C72*0.4 from the sheet "Sep 17" into cell C66 in the sheet "Oct 17" ends up pasting =$'Sep 17'.$C66*0.4 in cell C66, when all I want is C66*0.4.

I have been working around it by finding and replacing $'Sep 17'.$ with "", but it's slow when doing this multiple times over multiple sheets.

Is there a way to copy things over without the sheet reference?

Thanks for your time and help.

edit retag flag offensive close merge delete

Comments

What version of LibreOffice? Presumably, you do not really want C66*0.4 to be pasted in cell C66 because this would be a circular reference.

Jim K gravatar imageJim K ( 2018-03-06 09:04:03 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-03-06 09:02:51 +0100

Jim K gravatar image

The behavior you describe does not match my results. I tried several versions (LO 4.4, LO 5.4, AOO 4.1.3) and operating systems (Win10, Ubuntu, Mint). Here are the details:

  1. Source sheet cell B1 contains =A1*2. Paste to sheet 'Oct 17' cell B1 shows =A1*2.
  2. Paste Special (Ctrl+Shift+V) with Link checked shows =$'Sep 17'.$B$1.
  3. Source sheet cell B1 contains ='Sep 17'.A1*2. Paste to sheet 'Oct 17' cell B1 shows ='Oct 17'.A1*2.
  4. Source sheet cell B1 contains =$'Sep 17'.A1*2. Paste to sheet 'Sep 18' cell B1 shows =$'Sep 17'.A1*2.

I looked for a setting that may change this behavior to what you describe but could not find any. Are you sure that what you described is exactly correct? If so, maybe you are using an older version and need to upgrade.

edit flag offensive delete link more

Comments

Hi Jim - thanks for your testing and reply. I had a really old version of LO, so upgraded, but the behavior is the same, although it sometimes doesn't do it.

Is there a way to turn off having any kind of sheet reference in the cells, so that, eg $'Sep 17'. NEVER shows up in the cells, unless I want it to? I have no need for that functionality.

Thanks again for your time and help.

thomoz gravatar imagethomoz ( 2018-03-09 00:50:13 +0100 )edit

"Is there a way to turn off having any kind of sheet reference in the cells?" I doubt it; this is how Calc addresses work.

Jim K gravatar imageJim K ( 2018-03-09 14:17:45 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-03-06 04:15:09 +0100

Seen: 36 times

Last updated: Mar 06 '18