Bug 63087 - Detective "Trace Precedents" or "Trace Dependents" pointing to a different tab does not provide reference cell
Summary: Detective "Trace Precedents" or "Trace Dependents" pointing to a different ta...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: Other All
: high major
Assignee: Not Assigned
: 65786 104124 (view as bug list)
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
Reported: 2013-04-03 20:15 UTC by stealthsub2
Modified: 2020-11-26 07:36 UTC (History)
23 users (show)

See Also:
Crash report or crash signature:

Image of Spreadsheet entry box showing cell trace location (8.23 KB, image/png)
2013-11-29 06:18 UTC, stealthsub2
example ods showing the problem (9.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-11-05 11:37 UTC, Sandro Santilli
temptative patch (1.46 KB, patch)
2015-11-05 14:56 UTC, Sandro Santilli

Note You need to log in before you can comment on or make changes to this bug.
Description stealthsub2 2013-04-03 20:15:37 UTC
I see that Trace Precedent works to find a precedent cell on the same tab (ie, "Once you have defined a trace, you can point with the mouse cursor to the trace. The mouse cursor will change its shape. Double-click the trace with this cursor to select the referenced cell at the end of the trace.").

But it does not work to trace a precedent cell that is on a different tab.  I used to use this function in Excel all the time.  Hope a fix can be developed.
Comment 1 Dominique Boutry 2013-11-28 09:20:25 UTC
LibO on Win7 :

My findings :
- Tracing the precedents which are on a different sheet works (other sheet symbolized with a diamond),
- but when the cursor hovers the trace, it doesn't change its shape, it doesn't provide a way to select the cell in the other sheet,

I hope this effectively reproduce the current bug file.

Additionaly, if one switches to the other sheet, makes a modification then switch back to the first sheet, the special arrow (with the diamond) is redrawn at an incorrect place (insert a large enough new first column to see it, and "Tools > Detective > Refresh traces" to redraw correctly). May be it is an accepted/planned behaviour.
Comment 2 stealthsub2 2013-11-29 06:18:53 UTC
Created attachment 89974 [details]
Image of Spreadsheet entry box showing cell trace location
Comment 3 stealthsub2 2013-11-29 06:22:44 UTC
I confirm all of your findings.

The "entry box" does show the correct sheet location and cell # (ie, Sheet2.F26)
[see attached file]

but, as you confirm, the arrow trace will not change to be able to click to the "sheet2.F26" location.
Comment 4 stragu 2013-12-12 06:14:08 UTC
I confirm that the "different sheet trace" is not clickable in either.
Comment 5 stragu 2013-12-12 06:45:23 UTC
However, in, there is no arrow to the "entry cell" nor the "source cell" in a different sheet as a result of either Trace Dependents or Trace Precedents respectively. This apparently was fixed for the 4.1 branch?
Comment 6 Ralf 2014-03-23 19:35:51 UTC
I consider this bug or rather missing feature to be very important. I find it impracticable to understand complex spreadsheets without a "detective", which works across worksheets. Therefore, I consider MS Excel to be necessary for understanding a complex spreadsheet, even if one works primarily with LibreOffice Calc.

Therefore, I consider this bug to be a candidate for the website "Really basic missing features and enhancements" (https://wiki.documentfoundation.org/Development/Really_basic_missing_features_and_enhancements), but I do not know how to nominate the bug for that website. Instead I will nominate it for mab4.1.

As the bug is related with the detective "Trace Dependents" as well, I change the title and try to be more specific with the title.

I can confirm the bug for LibreOffice with Windows 7 and for LibreOffice with Ubuntu Linux. Hence I change the platform specification to "All".

I checked that the bug is inherited from OpenOffice.org. I change the version of first occurrence accordingly.

Though I find the bug very important, I would classify it as enhancement. I change the importance specification accordingly.
Comment 7 Joel Madero 2014-03-23 19:58:52 UTC Comment hidden (obsolete)
Comment 8 Julien Nabet 2014-03-23 20:47:53 UTC
On pc Debian x86-64 with master sources updated today, I can reproduce this.

- open a brand new file on Calc
- Sheet1, select cell A1, type "5" (without the quotes)
- Add another sheet (just name it "Sheet2") to the file
- On Sheet2, cell C4, type "=Sheet1.A1"
- On Sheet2, select cell C4, then "Shift-F7" (equivalent to "Tools/Detective/Trace Precedents")

1) There's no icon to go to the "precedent" cell (corresponds to initial description)
2) If you go to Sheet1 + come back to Sheet2, the arrow has moved (corresponds to part 2 of comment 1)

Ralf: to answer your comment6, about https://wiki.documentfoundation.org/Development/Really_basic_missing_features_and_enhancements, I think you just need to add a paragraph on the wiki page. (it requires an account to do it)
Comment 9 stealthsub2 2014-03-24 04:50:31 UTC
(In reply to comment #7)
> I am removing this from the MAB list, the reason for this:
> 1. I do not see easy reproducible steps - while the first entry has some
> stuff "once you have defined a trace" -- please explain what this means.
> 2. Enhancements do not belong on MAB
> 3. The bug is still set to UNCONFIRMED - and should be left as UNCONFIRMED
> until clear steps are provided.
> But the most serious is #2 - enhancements never belong on the MAB list.
> Please do not add bugs to the MAB list unless you know the procedures in
> place - else it just leads to QA members having to commit their time to
> remove and explain. Thanks
> If you're interested in learning about procedures feel free to join the QA
> mailing list or jump into the IRC channel

>>>>> Joel,
in point 1, you ask "once you have defined a trace" -- please explain what this means."
I copied this from the LibreOffice Help on the Detective command:  http://screencast.com/t/1eH1YarD

>>>>> All,
(1) With reference to point #3 (... still set to UNCONFIRMED), Dominique, chtfn, and Ralf have confirmed my original observation (Trace does NOT work if the Dependent cell is on a different tab).
Please ensure that ALL references to this request are labeled as CONFIRMED.

(2) Terminology: Bug vs enhancement vs "repair / fix".
OK, I can see that calling this a bug may not be the best term to use.
But, I don't think it should be called an enhancement either.
To me, an enhancement would be to add a new feature that does not currently exist.
Since the Detective command already exists but does not work as defined, I think a correct term would be something like "fix" or "repair".

Excel is able to find dependents located on different tabs.
I hope that a LibreOffice Calc "fix" can be developed for this issue.

Thanks, Barry
Comment 10 Ralf 2014-03-24 10:27:32 UTC
(In reply to comment #9)
> (In reply to comment #7)

> (2) Terminology: Bug vs enhancement vs "repair / fix".
> OK, I can see that calling this a bug may not be the best term to use.
> But, I don't think it should be called an enhancement either.
> To me, an enhancement would be to add a new feature that does not currently
> exist.
> Since the Detective command already exists but does not work as defined, I
> think a correct term would be something like "fix" or "repair".

I fully agree. I take back my switch to "enhancement" and reinstate the original classification "normal".

> Excel is able to find dependents located on different tabs.
> I hope that a LibreOffice Calc "fix" can be developed for this issue.

True. I consider this bug as serious show-stopper for switching fully from MS Excel to LibreOffice Calc, if a user works with complex spreadsheets containing several links between worksheets/tabs. Obviously the procedures for bug classification are opaque to me and my nomination of this bug for mab4.1 was not perceived to be constructive. However, if others share my importance assessment of this bug, someone with more knowledge of the bug classification procedures might want to provide higher visibility for this bug. Maybe a change of the importance setting is appropriate.
Comment 11 Joel Madero 2014-03-24 13:44:17 UTC
Please also don't call bugs "serious show-stoppers" - I know for you it might be but honestly if you look through the THOUSANDS of bugs you'll see that many of them call themselves "serious show stoppers". In order to accurately and objectively triage bugs you must look at the project as a whole. For instance a "serious show stopper" would be a crasher bug that affects many users. For more details on appropriate triaging prioritization please see our flowchart. 

This bug still doesn't have everything done to it that it needs for MAB . . . please stop adding bugs to the list unless you've carefully read and understood the steps.

I'm not going to remove this but IMHO this is not a MAB bug - if another dev agrees please remove this. The reason being that it's been around literally since the beginning of the project and we have a total of 3 users saying it's a bug 3 years after the project has begun. If it was affecting that many users we would have heard more about it. But I've removed it once already and it was readded (again without all the steps done). At best this is a minor bug IMHO
Comment 12 Ralf 2014-03-24 18:19:47 UTC
Joel, thank you for clarifying that this bug is not a MAB. Unfortunately, I was not aware of the flowchart mentioned by you above when I nominated the bug vor mab4.1. I apologize for unnecessary noise.

If my language was too colourful, I take back the expression "serious show-stopper". I acknowledge that you have a much better judgment in comparing bugs considering your experience with a vast number of bugs. Nevertheless, please consider the whole sentence containing the mentioned expression. The sentence is a personal judgment and it applies the expression to a limited number of cases and users, i.e. to "switching fully from MS Excel to LibreOffice Calc, if a user works with complex spreadsheets containing several links between worksheets/tabs."

According to the "Activity log for bug 63087" under the modification history above, the bug was added back to mab4.1 by yourself just 54 seconds after you had removed it from mab4.1. I assume that the system showed an unexpected behaviour by adding it back when you were logged in. The activity log is difficult to understand because you are not aware of having added it back with your account. Please understand that I do not want to fiddle around with the status of the bug now, as the system seems to react unexpectedly. Can I ask you to have a second try in removing the bug from the mab4.1 list, please? Sorry for the hassle. You might also consider marking bug 65786 as duplicate of this one.

I agree that the late reporting of this bug is a strong indication that not many users consider it to be very important. However, if one searches the internet for noise about this bug then you find some. People were bringing forward this OpenOffice.org bug in user forums already before LibreOffice was founded. We do not know if everyone accepted the bug or if the bug was sometimes even a reason for switching back to MS Excel. Moreover, I assume that only a small fraction of users makes an effort to set up a Bugzilla account and to report bugs.

We will never know this statistics. I can only specify my personal experience with this bug. I often work with LibreOffice Calc. If I work on a complex spreadsheet with many links across worksheets/tabs, I do not see the option to work solely with LibreOffice Calc. I need MS Excel at least for checking and understanding links across worksheets/tabs, because the "Detective" is a necessary tool for me, also concerning links across worksheets/tabs.
Comment 13 Julien Nabet 2014-03-24 22:32:02 UTC
After some research, here's a codepointer:
The difference between ok (all in 1 sheet) and ko (the ref in another sheet) is represented by treatment dependent on bFromOtherTab variable.
Comment 14 raal 2014-09-21 05:57:14 UTC
*** Bug 65786 has been marked as a duplicate of this bug. ***
Comment 15 QA Administrators 2015-10-14 19:56:50 UTC Comment hidden (obsolete)
Comment 16 Ralf 2015-11-04 22:06:09 UTC Comment hidden (obsolete)
Comment 17 Sandro Santilli 2015-11-05 11:37:45 UTC
Created attachment 120285 [details]
example ods showing the problem
Comment 18 Sandro Santilli 2015-11-05 11:53:55 UTC
still present in current master branch 
(commit ffde331fbefec360d0f6ca5268cdc9acd1a6440b)
Comment 19 Sandro Santilli 2015-11-05 14:56:34 UTC
Created attachment 120290 [details]
temptative patch

I'm attaching a temptative patch for the problem. It does NOT provide click-to-follow behavior, but does draw arrows in the "alien" sheet
Comment 20 Sandro Santilli 2015-11-05 15:03:01 UTC
NOTE: patch is too lame, doesn't work on multiple references either...
Comment 21 QA Administrators 2016-11-08 11:58:16 UTC Comment hidden (obsolete)
Comment 22 Ralf 2016-11-08 20:19:42 UTC
(In reply to QA Administrators from comment #21)

The bug is still present (LibreOffice, 32-bit version, Windows 10).
Comment 23 Vitaly 2016-11-11 15:46:33 UTC Comment hidden (me-too)
Comment 24 Buovjaga 2016-12-02 07:17:37 UTC
*** Bug 104124 has been marked as a duplicate of this bug. ***
Comment 25 Steve Cahill 2017-07-17 22:05:01 UTC
For those using the LibreOffice calc tool for large projects (farm management, complex taxes) where lots of data references cross many worksheets or separate spreadsheet files, being able to look at a number and audit/inspect its chain of sources is a necessary feature; lacking that, Excel remains the only tool that can do the job. A good use-case example: You want to do your taxes, you've been keeping your records in a spreadsheet file. You download an Excel-format 1040 tool (Glenn Reeves's for example) and you want to create links to your existing spreadsheet work from the new 1040-formatted spreadsheet, then check that data precedents were set correctly. Then you might want to update your original source spreadsheet, you break something, and you need to find the why-broken issue. All this is routine in Excel, but is not possible in LibreOffice Calc V5.3.4.2 (x64) running on Windows 10 Pro. Lots of good things in this tool, but I have to stick with Excel as long as this remains a limitation.
Comment 26 Andrea Ferrari 2018-05-28 08:47:11 UTC
Issue still present in Libreoffice Calc (x64)
Comment 27 bunkem 2018-08-08 22:41:53 UTC

I am trying to audit a multi-sheet/tab spreadsheet and the fact that this functionality being missing is disappointing.  Right now in 6.2 alpha, you can see that there is something linked as a dependent on another sheet but have no way to find it.  (I assume that is what the line with a box is to mean??)

In my situation, I have ~20 sheets so hoping to find a connected cell is impossible or perhaps just plain luck.

This issue has gone unactioned by OOo (Bug 44828) since March 2005.  Interestingly people spent the first five years arguing if it was truly a bug. LOL  Now nothing has happened to fix this for 13 years there and 5 years here.

Any chance that this can be elevated in the bug fix list?

Without a fix as @Steve Cahill has mentioned, functionality forces the more serious spreadsheet user to go back to Excel.  Such a shame.

Please fix.
Comment 28 Gessel 2018-11-07 12:14:34 UTC
Either copy excel's as people will know it or click/control-click/right-click the box at the end of the dependent indicator to at least show the dependent cells if not actually navigate there....
Comment 29 Barry McMullin 2019-04-11 11:45:48 UTC
Given early discussion on priority of this, I just want to log another +1 that this does affect me, and does limit my usage/deployment of localc.
Comment 30 Brandon 2019-08-01 23:37:19 UTC
+1 for this being important functionality. Encountered on
Comment 31 Will Braswell 2019-09-13 17:05:28 UTC
+1 this should be listed as CRITICAL because it really is a serious bug!

As noted, this is a carry-over from OpenOffice.org:


The bug is also discussed here:


Can someone please give an update on when this bug will be fixed???
Comment 32 Julien Nabet 2019-09-16 07:02:34 UTC
Eike: I increased priority of this one because even if it's not a crash or a regression, it's a current use case which is a bit blocking for lots of people.
The goal is to increase visibility of this long standing bug.
(Don't hesitate to revert my changes if you think I'm wrong here)
Comment 33 Richard Vendelbo Nielsen 2019-11-23 09:40:58 UTC
+1 for this one as well. This is keeping me from making anything more than simple workbooks in Calc.
Comment 34 gte.ejecutivo 2020-02-14 20:35:06 UTC
This is, imho, the most important issue if LO is to be considered a replacement for Excel in business. Without it, only simple sheets can be reviewed on a timely basis.

A workaround for tracing dependents in other sheets is to find/replace using regular expressions as follows:


where, for a given cell sheet.A1:
   sheet is the sheet name
   A is the column
   1 is the row

The regular expression characters used:
   \ to take the period as a literal
   .* any character any number of times (to search with/without absolute 
      references - $)

It's not perfect (it will find some references that are not the cell in question) buy won't miss any case.
Comment 35 Steve Cahill 2020-02-16 01:36:06 UTC
Re the work-around offered in comment #34: yes, good for tracing dependents in other sheets, though it might be too labor-intensive for some. The work-around, while useful for tracing dependents, leaves the precedent-identification/efficient-navigation part of the problem unaddressed, though. IMHO LO is not going to compete with Excel until the capability to do efficient point-and-go navigation for both dependents and precedents, as Excel does, is addressed.
Comment 36 George Theodoridis 2020-11-04 09:50:42 UTC
I have an office of 20+ people.
I want to move from Excel to Calc.
An inability to trace between sheets is a deal breaker.
It is essential to be able to validate the construction of spreadsheets as giving accurate information / the correct data is being linked to for calculations.
It is simply not viable in a business environment to hope you did it correctly and not be able to check your work.
Clients are lost this way.