[Calc] Relative references behavior for worksheets

users
#1

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.

#2

Having sheets named Sheet1, Sheet2, Sheet3 etc. any reference from
Sheet2 to Sheet1 or from Sheet3 to Sheet2 refers to the _previous_
sheet. When you copy this reference to the first sheet, then there is no
previous sheet which is why you get #REF!.X1
Any absolute reference to $Sheet1 refers to the first sheet. Since there
is always a first sheet, this reference can be copied anywhere. When you
copy an absolute reference to $Sheet3 (3rd sheet) into a document with
only 1 or 2 sheets you get #REF!.X1 because there is no third sheet.

#3

Having sheets named Sheet1, Sheet2, Sheet3 etc. any reference from
Sheet2 to Sheet1 or from Sheet3 to Sheet2 refers to the _previous_
sheet. When you copy this reference to the first sheet, then there is no
previous sheet which is why you get #REF!.X1
Any absolute reference to $Sheet1 refers to the first sheet. Since there
is always a first sheet, this reference can be copied anywhere. When you
copy an absolute reference to $Sheet3 (3rd sheet) into a document with
only 1 or 2 sheets you get #REF!.X1 because there is no third sheet.

You are explaining the difference between absolute and relative
references to worksheets. I already know how it works. I am interested
in users replicating the test I presented and commenting on whether
they would rather see a different default behavior in Calc (as it
happens in other spreadsheet programs).

Let me express the matter with different words. Currently, the default
behavior would give:

Sheet2.A1: =Sheet1.A1

What I am saying is that the _default_ behavior is problematic,
inconvenient and unexpected by users. Instead, the _default_ behavior
should give:

Sheet2.A1: =$Sheet1.A1

Meaning, the _default_ behavior for cells should remain, using relative
references, but the _default_ behavior for sheets should be modified,
to use absolute references (note the "$" in front of the sheet name,
and no "$" for the column nor for the row of the cell).

By changing the _default_ behavior:
_ users would get the "traditional" (expected) behavior;
_ there would be less confusion among users (sometimes seeing "#REF!"
errors, and sometimes without understanding why their worksheets are
failing);
_ in case relative references to sheets are actually needed by a user,
the correction is easier from absolute to relative notation than the
other way around.

I hope I am explaining my point clearer now.

Regards,
Ady.

#4

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.

#5

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?

#6

Ady wrote:

Having sheets named Sheet1, Sheet2, Sheet3 etc. any reference from
Sheet2 to Sheet1 or from Sheet3 to Sheet2 refers to the _previous_
sheet. When you copy this reference to the first sheet, then there is no
previous sheet which is why you get #REF!.X1
Any absolute reference to $Sheet1 refers to the first sheet. Since there
is always a first sheet, this reference can be copied anywhere. When you
copy an absolute reference to $Sheet3 (3rd sheet) into a document with
only 1 or 2 sheets you get #REF!.X1 because there is no third sheet.

You are explaining the difference between absolute and relative
references to worksheets. I already know how it works. I am interested
in users replicating the test I presented and commenting on whether
they would rather see a different default behavior in Calc (as it
happens in other spreadsheet programs).

Let me express the matter with different words. Currently, the default
behavior would give:

Sheet2.A1: =Sheet1.A1

What I am saying is that the _default_ behavior is problematic,
inconvenient and unexpected by users. Instead, the _default_ behavior
should give:

Sheet2.A1: =$Sheet1.A1

Meaning, the _default_ behavior for cells should remain, using relative
references, but the _default_ behavior for sheets should be modified,
to use absolute references (note the "$" in front of the sheet name,
and no "$" for the column nor for the row of the cell).

I don't know about Andreas, but when I read your original steps:

3_ In Sheet2 -> A1 : =Sheet1.A1

I thought you meant type "=Sheet1.A1" in cell A1 of Sheet2, and expected that to behave as an absolute reference (which would be inconsistent with row and column references). From the above description, about "default behaviour" giving "=$Sheet1.A1", it sounds like you're talking about the behaviour when clicking on a cell as a shortcut to creating a reference?

When typing a formula directly, it's reasonable to expect the user to be responsible for getting the syntax right, including using $ to create absolute references where necessary. It may be more reasonable to expect that references generated by clicking in cells be tailored to the more common need - whatever that may be. I can imagine working out what's most common isn't be easy though, and will probably never please everyone ;o)

Personally, I don't often copy entire sheets, and when I do I probably do usually want absolute references to other sheets as you suggest. On the other hand, I can also see the use of relative sheet references, e.g. for things where there's a sheet for each month with totals carried over from one month to the next. I couldn't really say which is the more common use overall, and therefore which should be the default. Another consideration is that changing the current behaviour may confuse those who are used to the way it works at the moment and find it convenient.

By changing the _default_ behavior:
_ users would get the "traditional" (expected) behavior;

Unless it's changed recently, it seems "traditional" for LibreOffice (and therefore probably "expected" for at least some of its users) is to create relative sheet references.

#7

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.

#8

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.

#9

I don't know about Andreas, but when I read your original steps:
> 3_ In Sheet2 -> A1 : =Sheet1.A1
I thought you meant type "=Sheet1.A1" in cell A1 of Sheet2, and expected
that to behave as an absolute reference (which would be inconsistent
with row and column references). From the above description, about
"default behaviour" giving "=$Sheet1.A1", it sounds like you're talking
about the behaviour when clicking on a cell as a shortcut to creating a
reference?

Correct, I was referring to "clicking" on other sheets, and their
cells. I have just sent another email to make it more clear. Apologies
for the confusion.

When typing a formula directly, it's reasonable to expect the user to be
responsible for getting the syntax right, including using $ to create
absolute references where necessary. It may be more reasonable to expect
that references generated by clicking in cells be tailored to the more
common need - whatever that may be. I can imagine working out what's
most common isn't be easy though, and will probably never please
everyone ;o)

Indeed, 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.

Personally, I don't often copy entire sheets, and when I do I probably
do usually want absolute references to other sheets as you suggest. On
the other hand, I can also see the use of relative sheet references,
e.g. for things where there's a sheet for each month with totals carried
over from one month to the next. I couldn't really say which is the more
common use overall, and therefore which should be the default. Another
consideration is that changing the current behaviour may confuse those
who are used to the way it works at the moment and find it convenient.

Certainly having relative references to sheets is useful. I am not
saying it is not. My issue is about the _default_ behavior.

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 should be as I am suggesting (and as other programs
already do / behave).

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".

> By changing the _default_ behavior:
> _ users would get the "traditional" (expected) behavior;

Unless it's changed recently, it seems "traditional" for LibreOffice
(and therefore probably "expected" for at least some of its users) is to
create relative sheet references.

I meant "traditional" as in spreadsheet programs that are being used
for more than 2 decades. "Traditional" for "old" users of spreadsheet
programs. I guess you are correct about "traditional" being different
when talking about Calc (which is in fact the conflict I am talking
about). I am focusing on users and practical functionality, more than
in the program (Calc).

As of the more common / frequent use of relative or absolute references
for sheets, we probably cannot really know. And yet, since it is easier
to correct (in case the user really needs such correction) from
absolute to relative references to sheets than the other way around,
the suggested change in default behavior makes at least some sense.
There is a reason why this issue keeps coming back to forums and alike,
and even reported as if the behavior would be an actual bug (IMO, it is
an inadequate default behavior, but it is not a complete "bug", but
close to it :).

Regards,
Ady.

#10

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

Apologies for not being clear enough.

Indeed, I am talking about the default behavior when not explicitly
typing in the formula. This includes clicking on sheets, cells, and
even external documents, and it includes wizards / assistance.

Some users are used to type-in the whole formula, or edit "by hand".
Some users are used to formula wizards / assistance and mouse (click,
drag, select, fill...). For some users, this is especially relevant
when using complex long formulas involving multiple sheets, multiple
ranges of cells, or multiple cells, as it reduces the chances of typos.

Regards,
Ady.