Ah! My response was based on entering the whole formula through the keyboard. That's my modus
operandi (sp?).
You're talking about using the mouse to generate references. I'll back away as I have nothing
useful to say about your method.
--
Jim
-----Original Message-----
From: Ady <ady-sf@hotmail.com>
To: users@global.libreoffice.org
Sent: Wed, 22 Apr 2015 14:09
Subject: 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
--
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
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: 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.