Duplicate Sheet changes Sheet References

English
#1

Hi,

I’m trying to copy a sheet that has formulae that references other sheets. When I duplicate the sheet all the cell references that refer to other sheets change and this appears to be dependant on where in the sheet list the sheet appears. For example, if a cell in Sheet 2 references Sheet 1, if I duplicate Sheet 2 and place the duplicate after Sheet 2 then all the cell references in Sheet 3 that should be pointing to Sheet 1 point to Sheet 2.

Is there a way to prevent this from happening? As the structure of the sheets is in 99% of spreadsheets completely arbitrary I find this a bizarre limitation on methods of working, and certainly Excel does not function in such a way. I should imagine that the vast majority of users who duplicate a sheet do so in order to test some variations or create a new profile or year or some other identical calculation, so want the sheet references to remain the same. I can’t think of a single application where duplicating a sheet would require a relative shift in sheet references (would be interested to know!)

Just to be clear I am not referring to absolute/relative cell references, I am referring to the sheet references that preceed the cell references.

Many thanks

#6

Copying the duplicate of Sheet 2 (Sheet 2_2) before Sheet 2, will keep formulas pointing to Sheet 1, but will not keep formulas pointing to Sheet 3, but to Sheet 2.

#2

This is exactly absolute/relative cell references what you are talking about. Placing $ sign before sheet name gives you an absolute reference to the sheet, while using sheet name without $ sign gives you a relative reference, the same as with cells.

Placing =$Sheet1.A1 and =Sheet1.A1in cells on the Sheet2 and then duplicating Sheet2 to Sheet3 placing Sheet3 after Sheet2 will evaluate furmulas in Sheet3 to =$Sheet1.A1 and =Sheet2.A1 respectivly. By the way, Excel implements the same behaviour.

#3

Excel does not implement this same behaviour - placing a $ in front of the sheet reference breaks the formula in Excel. When building a slightly different report it is common to create a duplicate of an existing report and then start adapting it - this is quick and easy in Excel and I have been working like that for years.

So in order to have an excel spreadsheet compatible with calc, one would need to go through and change all the sheet references to place a $ in front of each one? And how would this be done in one fell swoop? I can see how to do it quickly for each sheet referenced, but if there are many different sheets referenced it would be easy to do a find and replace on 20 sheets referenced and miss the 21st. Surely this is not the only option? Does saving keeping the file as an xls stop this behaviour?

#4

By default, LibreOffice prepends sheet names with $ on creation, or on import. This allows to have the compatibility with other formats/applications that lack support relative sheet referencing.

How did you created the references in your sheets? Did some built-in operation in LO made all those without the $ without you explicitly chose the format? If so, possibly that’s a bug - please file it to bug tracker and explain how to reproduce.

If you created them manually, though, not knowing that LibreOffice supports more features than your familiar application, that is not a program error. You may use e.g. F4 to change the types of references … or use Find & Replace.

#5

OK great thanks! So this is solved. Yes I had developed the spreadsheet from scratch in LO unaware of that LO “supports more features”.

Solution -

  • Save as an xlsx
  • Re-open it
  • Copy the required sheet
  • Find and replace all $’ with ’
  • Save back to an ods

If anyone has ever used relative sheet referencing to good effect I would love to hear of it. Seems like a solution that benefits almost nobody and hinders everyone else.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.