Hi Ady, I have practically zero experience with "other spreadsheet" programs and such experience as I do have has me comparing them against LO (or OOo) not vice versa.
That said, it is my OPINION that intersheet references should be of the class specified (absolute if entered with a leading $ and relative if entered naked). If one or more new sheets are inserted or removed the sheet references should be modified applying the same rules as are used with regard to column (row) references when one or more columns (rows) are inserted or removed. Any spreadsheet program (be it MSO Excel, KingSoft spreadsheet, or even OOo or LO Calc) operates differently than this then IMNSHO they got it wrong. Where am I wrong? -- Jim -----Original Message----- From: Ady <[email protected]> To: [email protected] Sent: Wed, 22 Apr 2015 6:45 Subject: [libreoffice-users] [Calc] Relative references behavior for worksheets Hello, 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 course. 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 as possible. TIA, Ady. **** 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), "=Sheet2.A1". 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 notation. 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 easier. TIA, Ady. -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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 -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 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
