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 3.5.4.2 Build ID: 350m1(Build:2)
Can you name a version where this worked?
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
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.
If you manually type in it works fine - so it's just the shortcut that's doing some weird stuff. Confirmed Ubuntu 13.10 GNOME LibreOffice 4.3 build a few days ago New 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. Possibly: ProposedEasyHack as it seems like just the shortcut is borked
EasyHack code pointer: look into sc/source/core/tool/reffind.cxx ScRefFinder::ToggleRel()
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?
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()?
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: https://gerrit.libreoffice.org/11218
Remove depends on bug 83481 because this one can be fixed without the other one
Seems the notification bot was offline when this was committed to master with 5bc9fdf51d4ca545371b4e610858b73d076350f6
Migrating Whiteboard tags to Keywords: (EasyHack DifficultyBeginner SkillCpp ) [NinjaEdit]