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

> Replicating your steps, I get a copy of Sheet2 at position #1 and any
> relative reference to the previous sheet needs to raise a #REF! error
> because you told me to reference a cell on the previous sheet. The error is
> perfectly clear, wanted and informative. There is nothing wrong with errors.
> Errors are not a slap in your face. They are not errors because you are
> stupid. I can not see any error in the application. Everything works as
> expected. If you reference something something irreferenciable you get a
> #REF! error.
 
I seem to have a problem explaining the matter.

I do understand how it works. I understand the error. I understand why 
it is giving this error. I am not complaining about receiving an error.

I'll try to explain my point in a different way.

Let's assume the following procedure:
1_ Click on A2.
2_ Type in an equal sign, "=".
3_ Click on A1.
4_ Press [Enter]
5_ Click on A2.

The resulting formula is:
A2: =A1

The default behavior, as seen in this simple procedure, is that cells 
are referenced with relative notation.

If the resulting formula would had been (by default and with no 
additional steps/help):
A2: =$A$1

we would had concluded that the default behavior was absolute 
references. But we know this is not the default behavior, and users 
take advantage of this. All spreadsheet programs that I know of behave 
in this same way. To receive a formula with absolute references in Calc 
we would need some extra step(s) (e.g. [Shift-F4]).

Now let's repeat the procedure, with a slight difference:
1_ Click on Sheet2.A2.
2_ Type in an equal sign, "=".
3_ Click on Sheet1.
4_ Click on Sheet1.A1
5_ Press [Enter]
6_ Click on Sheet2.
7_ Click on Sheet2.A2.

The resulting formula is:
Sheet2.A2: =Sheet1.A1

Here, once again, the cell references are, by default, relative. Since 
we now involved multiple sheets in the formula, the resulting formula 
includes the sheet(s) as part of the reference.

And we also see that, by default, the reference to "Sheet1" is also a 
relative one. Here is where Calc goes differently than other 
spreadsheet programs, and it is here where users (that are used to 
other spreadsheet programs) are having problems (and even reporting 
this behavior as a bug, multiple times already, since they don't 
understand why it is failing, considering that they are used to a 
different default behavior).

When using other spreadsheet programs, the resulting formula for the 
last procedure would had been: "absolute reference for 'Sheet1' and 
relative reference for its 'A1' cell". Or, using Calc's notation:
Sheet2.A2: =$Sheet1.A1

(note the "$").

What I am trying to convey is that Calc should change the default 
behavior for referencing sheets, so to behave as other spreadsheet 
programs do.

I am not saying that:
Sheet2.A2: =Sheet1.A1
is wrong, or that I don't understand the "#REF!" error, or that I don't 
understand why the error is being generated after the copy+paste 
procedure I described in my initial email. I am saying that the 
_default_ behavior should be to obtain absolute references to sheets 
(while keeping relative references to their cells).

I am not suggesting to change the meaning of the "$" in front of the 
sheet. I am not suggesting to change the behavior of the "REF!" error 
nor its meaning.

I _am_ suggesting that, by default, the sheets in Calc should be using 
the "$" in front of them.

If a user wants to use relative notation for sheets, then such result 
should had been obtained by adding some step (e.g. explicitly deleting 
the "$" in front of the sheet reference), instead of obtaining a 
relative reference to the sheet by default, as it is now.

By changing the default behavior regarding default references to 
sheets, Calc would be simply imitating what other worksheet programs 
already do, and less "REF!" situation would be encountered by users.

Additionally, it is easier to replace absolute references to sheets 
with relative references to sheets, whereas it can be very difficult to 
find and correct every "REF!" in complex workbooks.

It is the *default* behavior of "references to sheets" that I am 
talking about. Hopefully I am making it more clear now.

Now, if my experience with other spreadsheet programs (as I described 
it here) is different than other users here in the list, I would like 
to know about it. If the tests / steps I have presented in this email 
thread cannot be replicated by others, or if the default behavior seen 
by others is different than what I am seeing, I would really appreciate 
receiving feedback about it, because it would mean that I could change 
the default behavior in my own setup, without waiting for developers to 
do anything.

Thank you in advance,
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