Bug 57929 - Switching between absolute & relative external references not possible
Summary: Switching between absolute & relative external references not possible
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5.4 release
Hardware: Other All
: low minor
Assignee: Laurent Balland
Whiteboard: target:4.4.0
Keywords: difficultyBeginner, easyHack, skillCpp
Depends on:
Reported: 2012-12-05 22:05 UTC by Gerry
Modified: 2015-12-15 23:36 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Gerry 2012-12-05 22:05:06 UTC
Problem description: It does not work anymore to switch between absolute & relative references in Calc if the reference comes from an external file. This used to work in previous versions, so it is a regression. In the tested version, Calc gives an error message "No references found".
IMHO this is a very often used feature in spreadsheet programmes, since many values may be imported from other files (e.g. financial calculations). This regression makes Calc almost useless when working with external references.

Steps to reproduce:
1. Create a new Calc spreadsheet and write the value "1" in cell A1. Save the file.
2. Create a second file
3. Type in the second file in cell A2 "=" and pick with the mouse the cell "A1" from the first file. You will see the value of the first file in the second file. So far, everything works fine. What you see is a relative reference.
4. Click in cell A2 in the second file and try to hit "Shift" & "F4". This should switch from relative references "='file:///test.ods'#$Sheet1.A1" to the absolute reference "='file:///test.ods'#$Sheet1.$A$1". This does not work anymore. You get the error message "No references found".

Cross-check: This feature still works if the reference is in the same file:
5. In the second file write the value "2" in cell B1.
6. Type in the second file in cell B2 "=" and pick with the mouse the cell "B1" in the same file. You see the value as relative reference.
7. Hit "Shift" & "F4" and you see that the relative reference switches to an absolute reference "=$B$1". 

System: Ubuntu 11.10, LibreOffice  Build ID: 350m1(Build:2)
Comment 1 Markus Mohrhard 2012-12-05 22:36:43 UTC
Can you name a version where this worked?
Comment 2 Gerry 2012-12-06 00:55:28 UTC
Hi Markus, I remember that I have used it in older versions. I cannot pinpoint it to one particlar version. Here in the forum (last comment) there is some indication with regards to the "change": http://forum.ubuntuusers.de/topic/openoffice-org-calc-absolute-betraege-mit-f4-/#post-1742737
Comment 3 Gerry 2013-01-28 19:46:34 UTC
Just as an update on this issue (of course, it is not a proof that it worked int he past):

The Handbook on Calc 3.4 (https://wiki.documentfoundation.org/images/b/b7/CG34-CalcGuideLO.pdf):
"To change references in formulas highlight the cell and press Shift-F4 to cycle
through the four different types of references. This is of limited value in more
complicated formulas; it is usually quicker to edit the formula by hand." 
--> they do not mention that is does not work for external references.
Comment 4 Joel Madero 2014-03-06 21:07:39 UTC
If you manually type in it works fine - so it's just the shortcut that's doing some weird stuff.

Ubuntu 13.10
LibreOffice 4.3 build a few days ago

Minor - doesn't prevent high quality work but can slow it down (as I said you can manually type in the $ signs and it works)
Low - likely not affecting many users, complex sheets referencing other files could be a headache but that's the minority of users.

ProposedEasyHack as it seems like just the shortcut is borked
Comment 5 Eike Rathke 2014-04-24 11:03:06 UTC
EasyHack code pointer: look into sc/source/core/tool/reffind.cxx ScRefFinder::ToggleRel()
Comment 6 Dan 2014-05-04 16:09:55 UTC
Thanks to Eike Rathke I was able to start debugging this and found that the line

>266: sal_uInt16 nResult = aAddr.Parse(aExpr, mpDoc, aDetails);

returns 0 for nResult when external references are used. This causes the if() statement to evaluate to false and so the reference is never toggled.

Tracing it into the "Parse()" function in address.cxx, it seems to get set at

>1197:  if (!pExtInfo)
>1198:      nRes = 0;

Unfortunately, I'm not sure how the "Parse()" function is supposed to be called (I can't find any documentation or comments in the code to explain its purpose), so I can't work out if this is a bug in the ScRefFinder::ToggleRel portion of the code, or the ScAddress::Parse function.

The value of aExpr that I'm getting when I debug this (at line 262 in reffind.cxx) is:

>(gdb) print aExpr
>$1 = "'file:///home/dan/Documents/ReferenceTo.ods'#$Sheet1.A1"

which looks like a valid reference to my inexperienced eyes. Can anyone give a n00b a pointer in the right direction?
Comment 7 Eike Rathke 2014-05-05 08:46:34 UTC
Hey Dan, that's a good start :-)

Now that you see that ScAddress::Parse() bails out because pExtInfo is NULL, wouldn't it be worth a try to pass a valid ExternalInfo* to Parse()?
Comment 8 Laurent Balland 2014-08-31 13:58:24 UTC
Proposed solution:
- retrieve external information during parse
- if it is an external ref, as I don't know how to retrieved an ScDocument pointer on external document, I bypass all information about file name and tab name and toggle only the row/column reference

It seems to work in all test case I used.
Please note that tab is always considered as absolute in an external reference, because there is no way to check the other tabs of the external document.

Under review as commit:
Comment 9 Laurent Balland 2014-09-04 18:42:31 UTC
Remove depends on bug 83481 because this one can be fixed without the other one
Comment 10 Eike Rathke 2014-09-17 17:05:52 UTC
Seems the notification bot was offline when this was committed to master with 5bc9fdf51d4ca545371b4e610858b73d076350f6
Comment 11 Robinson Tryon (qubit) 2015-12-15 23:36:25 UTC
Migrating Whiteboard tags to Keywords: (EasyHack DifficultyBeginner SkillCpp )