Bug 90799

Summary: Create absolute sheet references instead of relative when clicking while building formulas
Product: LibreOffice Reporter: Ady <ady-sf>
Component: CalcAssignee: Eike Rathke <erack>
Status: VERIFIED FIXED    
Severity: enhancement CC: erack, miguelangelrv, suokunlong
Priority: medium    
Version: Inherited From OOo   
Hardware: All   
OS: All   
Whiteboard: target:5.4.0
Crash report or crash signature: Regression By:
Bug Depends on:    
Bug Blocks: 108917    

Description Ady 2015-04-23 05:38:30 UTC 
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.
Comment 1 Eike Rathke 2015-04-23 15:22:19 UTC 
Might make sense to change creation of sheet references to absolute because that is what users are used to from other spreadsheet programs. I'm against yet another option to change the default though..

However, changing that should only be done individually at places where references are automatically created when clicking with the mouse or similar.
Comment 2 Ady 2015-04-23 17:38:44 UTC 
FWIW, there have been attempts to report this "unexpected" behavior in the past, and users that are used to the "expected" (as seen in other spreadsheet programs) behavior are having troubles identifying the real issue. This is especially relevant for users of other (popular) spreadsheet programs in which the default references to sheets are always absolute, not relative.

Such "old" users are seeing the problem when using the "copy/move sheet..." task. Under such situation, the resulting new (pasted) sheet has several "REF!" errors, so "old" users of other spreadsheet programs tend to think that the problem is with the "copy sheet" task itself, or with the formula not being copied "as expected". The _relative_ reference default behavior usually escapes the initial evaluation for these "old" users.

The "REF!" result is only one (evident) case. Some users (coming from other spreadsheet programs) would expect that copy+pasting a sheet would result in an "exact copy" (as with absolute references to sheets), and some users are unaware that the result is actually not an exact copy but a "relative" one.

The situation can be even more complex when copying sheets to other documents (as oppose to copying sheets inside the same workbook), as the position of the sheet can modify the whole result, and this situation is unexpected by unaware ("old") users.

What these "old" users don't realize at that moment is that what is really different is the "references to sheets" behavior (besides the fact that there is such a thing as relative references to sheets, they are the default ones in Calc), as I described in the initial description. Therefore, forum posts and alike are sometimes "answered" but "old" users cannot understand what's really "wrong".

Moreover, bug reports about this matter are recurrently being opened, but the reports are frequently misunderstood (mutually, by the initial reporter and by those who try to answer). One such example is bug #85538 <https://bugs.documentfoundation.org/show_bug.cgi?id=85538>.

I have had my own "REF!" experiences because of this "relative references to sheets as default" behavior, which is unexpected by users of other spreadsheet programs, who have been used to a different behavior (i.e. "absolute references to sheets as default behavior") for about 2 decades.

Replicating in other spreadsheet programs the 2 procedures I described in my initial description should help clarify the "expected" behavior regarding "references to sheets" and "references to cells in other sheets".

One thing that could also help users (in addition to this suggested change in behavior): adding and clarifying the Help information about relative references to sheets. In some other (popular) spreadsheet programs, an expression starting with "=Sheet1..." is treated as an absolute reference to "Sheet1", whereas in Calc such expression is by default a relative reference to "Sheet1", requiring the "$" symbol for it to be interpreted as absolute reference ("=$Sheet=...).

Having the possibility to choose between relative and absolute references to sheets is indeed useful. We ("old" users, or at least par tof them) just need an adjustment in the default behavior in Calc so to make its usage slightly more familiar to what we have been used to for 2 decades. (Note though, that there are some spreadsheet programs other than Calc that do include the possibility of relative references to worksheets.)

Whether with a new setting option, or with a new default behavior (or even without changing anything), the behavior in Calc needs to be much more clearly documented and published, so to not affect / confuse users coming from other spreadsheet programs, neither Calc users that might (only) be used to the current behavior.
Comment 3 m_a_riosv 2015-04-23 23:02:19 UTC 
I have been working with 123 since 1982 and it doesn't add an absolute sheet reference selecting with mouse. And as I remember Lotus-Symphony or the old Quattro Pro do the same.

IMO not a bug, at most a request for ¿enhancement?.

I'm sorry but many times we tend to think, that what it's fine for us, it`s fine for everybody, unfortunately much of the times not true.
Comment 4 Ady 2015-04-24 05:46:56 UTC 
(In reply to m.a.riosv from comment #3)
> I have been working with 123 since 1982 and it doesn't add an absolute sheet
> reference selecting with mouse. And as I remember Lotus-Symphony or the old
> Quattro Pro do the same.
 
 
Quoting from my prior comments:
"Note though, that there are some spreadsheet programs other than Calc that do include the possibility of relative references to worksheets."

So indeed, the behavior is not problematic for everyone. And yet, the massive popularity of certain specific spreadsheet programs (far more than any other) makes the current default behavior a problem for a relevant amount of users. It takes (a lot of) time for a relevant amount of users to understand why certain complex worksheets they are building in Calc are not working as they expected (aka. "as they are used to"). For these users, it is not just a matter of adapting their usage to new / different ways of doing things. It is a first matter of having difficulty understanding the reasons why their formulas are failing after copy+pasting a sheet (only _then_ to start adapting their usage methods and their formulas).

It is not a surprise that the issue comes back again and again in forums, lists, bug reports and other channels.

Now, as I already said, there are advantages of having the possibility of relative references to sheets, and some programs other than Calc also use them (not necessarily in the same way as Calc does). This is why I mentioned an alternative possibility: providing a setting / option for the user to select the preferred behavior.

Whichever the case, the behavior and usage of references to worksheets really needs more publicity / Help info, not only to help users in transitioning and converting their spreadsheet documents into Calc's native format, but also to promote this advantage feature of Calc in comparison to some other (popular) programs.
 
 
> 
> IMO not a bug, at most a request for ¿enhancement?.
 
 
Quoting from my prior comments:
"but it is not a complete "bug", although close to it :)"

When I open a report, I tend not to set the level / type of report by myself (I leave this to the developers); just as it would be inappropriate for some other reporter to set a level of "highly critical" (:-O) to his own report.

If the resolution is to add more Help information and nothing else, it would help some users.

If the resolution would be to change the current behavior, a relevant group of users would see it as an enhancement.

If the resolution would be to add optional settings for users to be able to choose their default behavior, it would certainly be an enhancement.

Whichever the case, there is currently a certain behavior. To some users, it is somewhat a problem. I agree it is not exactly a bug in the sense of a "crash" is, but the current behavior (and its related feature of relative references to sheets) can result in "incorrect copy+paste" (in the sense that the result is not the expected one for many users, and there is no easy way for them to notice it, especially in complex workbooks with multiple sheets).
 
 
> 
> I'm sorry but many times we tend to think, that what it's fine for us, it`s
> fine for everybody, unfortunately much of the times not true.

 
I opened this report based on recurrent forum topics and similar questions, and even recurrent "bug reports", in addition to my own "bad experiences" with the matter.

I am not saying that everyone should work in a certain way, and I am certainly not saying that "my way is the best and only valid way". I already mentioned that there are advantages of relative references being available in Calc.

All I am saying is: It is a behavior that is causing (recurrent) confusion to more-than-a-few users, and I think it deserves some attention and potential modifications / improvements.
Comment 5 QA Administrators 2016-09-20 09:33:54 UTC Comment hidden (obsolete)
Comment 6 Ady 2016-09-26 21:47:37 UTC 
Still relevant as of version 5.1.5.2. I have not tested it in 5.2.x.
Comment 7 Eike Rathke 2017-04-19 16:14:36 UTC 
*** Bug 107257 has been marked as a duplicate of this bug. ***
Comment 8 Commit Notification 2017-04-19 19:37:59 UTC 
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ca8f4a66e44aed731646066241cf2225bb9f8c2a

Resolves: tdf#90799 generate absolute sheet for pointer-selected references

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Kevin Suo 2017-04-21 03:40:11 UTC 
Verified fixed on master.
Any plan to backport on 5.3 branch, as this is a very important feature?

Version: 5.4.0.0.alpha0+
Build ID: d11746a8f4dfa443a404add08ff8bfe51590c607
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default; VCL: kde4; 
Locale: zh-CN (zh_CN.UTF-8); Calc: group