I had the intention to file a bug report for Calc, but then I thought
that maybe I should first ask for some user's feedback.
I would like to know if the behavior I am about to describe can be
replicated with several versions of LibreOffice and/or under different
configurations / scenarios / OSes. Other comments are also welcome, of
The following is the text of the bug report I was about to file in.
Apologies for its length; I have seen too many of these problems
already and I want to explain the problem (and its solution) as clear
1_ New Calc spreadsheet (aka workbook).
2_ For this test, we need at least 2 Sheets ("Sheet1" and "Sheet2") in
the new spreadsheet document.
3_ In Sheet2 -> A1 : =Sheet1.A1
4_ Right click on the "Sheet2" tab and select "Move/Copy Sheet...".
5_ In the "Move/Copy Sheet" dialog, select "Copy", then OK.
6_ Select the new "Sheet2_2 , A1" cell.
7_ Note that the content of "Sheet2_2 , A1" is "=#REF!.A1", and the
result being displayed is "#REF!".
8_ Right click again on the "Sheet2" tab and select "Move/Copy
Sheet..." (as in step #4 above).
9_ In the "Move/Copy Sheet" dialog, select "Copy", select "move to end
position", then OK.
10_ Select the new "Sheet2_3 , A1" cell.
11_ Note that the content of "Sheet2_3 , A1" is (this time),
12_ Note that "Sheet2" has been copied to "Sheet2_3" with _relative_
references for _sheets_; e.g. "Sheet2_3 , A1" is not an exact copy of
"Sheet2 , A1", as "Sheet2_3 , A1" contains "=Sheet2.A1" (pasted with
relative notation for the _sheet_), instead of containing "Sheet1.A1"
(as it would had been expected in other spreadsheet programs).
This test shows that the Sheets are being treated with _relative_
(reference) notation by default, as cells are.
There seem to be similar reports about "named range of cells", and with
copies to another spreadsheet file (instead of copying inside the same
file, as my test here does).
Additionally, changing the name of the sheets, from "SheetN" to
something else, doesn't change this behavior.
Although I understand the potential advantage in some cases, this
concept and behavior of "relative" references to Sheets is inadequate.
We have relative notation in/for _cells_ because there is a certain
standard for their references, a "series". But this is not true for
Sheets, nor for a named range of cells for that matter.
At first sight, and based on the above test, someone could potentially
propose to only allow copying sheets "to the end"; but such idea would
be wrong too. In my sample test, I only used one formula, retrieving
data from one sheet "from the left" and then copying the sheet "to the
left" (in the typical LTR display). But I could have multiple sheets
and I could be retrieving data from surrounding sheets from both sides.
The relative notation in cells works everywhere in almost all cases.
Applying the same concept and *default* behavior to sheets and named
ranges of cells is inadequate. I consider this to be almost a bug, and
I am certainly not the only one.
Using the "relative notation" concept (and behavior) on anything other
than common cells should *not* be the default behavior, and such
possibility should be optionally and explicitly selected by the user
when performing each "copy+paste" action, or by means of the adequate
In other words, please leave the "relative notation" as default for
cells only, and as optional selectable possibility for sheets and for
named ranges of cells. The default notation for sheets should be
*absolute references*. In fact, the only references that should default
to relative notation should be references to cells, and any other
references should default to absolute notation.
Copying a Sheet already containing a formula pointing to another Sheet
should not generate "#REF!" errors. Sheets' references should be
inserted as _absolute_ notation by default.
The current default behavior is UNexpected by users (since other
Spreadsheet programs do not behave in the same way) and it can easily
generate loss of data (especially in complex multi-sheet workbooks).
A different wording for this situation / proposal would be: when
building a formula pointing to cells from other sheets, the *default*
behavior in Calc should be that the sheet's reference should use
_absolute_ notation by default while keeping the default notation for
cells in _relative_ form. This is the default behavior I see in other
spreadsheet programs. The user should *not* have to manually convert
each reference to "absolute" notation for sheets while keeping the
"relative" notation for cells.
Please keep in mind that, after copy+pasting a sheet, correcting all
the resulting "#REF!" cells can be a complex task (and prone to
errors), whereas modifying references for sheets from absolute to
relative notation is much
To unsubscribe e-mail to: email@example.com
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
- [libreoffice-users] [Calc] Relative references behavior for worksheets · Ady
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy