https://bugs.documentfoundation.org/show_bug.cgi?id=90799
Bug ID: 90799
Summary: Default behavior of references to sheets when clicking
while building formulas
Product: LibreOffice
Version: unspecified
Hardware: Other
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
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
Note that I am explicitly not typing-in the formulas, but using the mouse.
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
then we would had concluded that the default behavior was absolute references
for cells. But we know this is not the default behavior, and users take
advantage of this. 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 the formula:
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 some copy+paste procedures. 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!" situations 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.
Explicitly typing a formula should be respected. If users explicitly type-in
"$A$1", or "A$1", or "$A1", Calc respects it (i.e. it does not change it to
relative references). If users explicitly type-in "$Sheet1.A1", or
"Sheet.$A$1", or whatever, then Calc should definitely respect the input. I
think we all agree that this is correct, adequate and expected. So I am not
suggesting any change in this regard.
I could understand the potential inconvenience with current users of Calc,
perhaps already expecting the "unusual" (as of other spreadsheet programs)
behavior.
To be clear, I am not expecting from Calc to change current formulas, or any
content. I would expect to change the default behavior for new editions. If a
user edits an old Calc document, what was done before is "done", but IMHO in
the same document the new behavior for new editions / new formulas (when
clicking on sheets' tabs) should be as I am suggesting (and as other programs
behave / already do).
Moreover, considering the lack of features in Calc for multiple-sheets
documents, my guess would be that such change in the default behavior would be
more than welcome by experienced users.
But, I could understand such change could be considered somewhat controversial
by someone. So, as an alternative, I would suggest introducing an option, so
the user could select which kind of references should be used by _default_ for
*sheets*: either relative (as Calc behaves currently), or absolute ones (as any
other program I know). Similar options regarding the _default_ behavior could
also be added for "cells' columns" and for "cells' rows", or for "cells".
As for which is the more frequent use of references for sheets, either relative
or absolute ones, we probably cannot really know. And yet, since it is easier
to correct (in case the user really needs such correction) references going
from absolute to relative references to sheets than the other way around, the
suggested change in default behavior makes at least some sense (in addition to
imitate what other programs have been doing for more than 2 decades).
There is a reason why this issue keeps coming back to forums and alike, and it
has even been reported as if the behavior would be an actual bug (IMO, it is an
inadequate default behavior, a design flaw, but it is not a complete "bug",
although close to it :).
I'll repeat this for clarity: It is the *default* behavior of "references to
sheets" when clicking on Sheets' tabs while building formulas (or in formula
wizards / assistant or similar) that I am talking about. The default behavior
for such cases should be to automatically use the "$" symbol in front of the
Sheet name.
TIA,
Ady.
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
