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

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.

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.

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.

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.

“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.