Re: [libreoffice-users] [Calc] Relative references behavior for worksheets

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 <ady-sf@hotmail.com>
To: users@global.libreoffice.org
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: users+unsubscribe@global.libreoffice.org
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: users+unsubscribe@global.libreoffice.org
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