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)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: high major
Assignee: Not Assigned
URL:
Whiteboard: reviewed:2022
Keywords: difficultyInteresting, easyHack, skillCpp
: 65786 104124 149891 (view as bug list)
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
 
Reported: 2013-04-03 20:15 UTC by stealthsub2
Modified: 2024-02-27 08:46 UTC (History)
29 users (show)

See Also:
Crash report or crash signature:


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

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

Also,
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 Stéphane Guillou (stragu) 2013-12-12 06:14:08 UTC
I confirm that the "different sheet trace" is not clickable in 4.0.5.2 either.
Comment 5 Stéphane Guillou (stragu) 2013-12-12 06:45:23 UTC
However, in 4.0.5.2, 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 4.2.3.1 with Windows 7 and for LibreOffice 4.1.3.2 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.

Steps:
- 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. 
https://wiki.documentfoundation.org/File:Prioritizing_Bugs_Flowchart.jpg

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:
http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/detfunc.cxx#InsertArrow
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 5.2.3.3, 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 6.0.4.2 (x64)
BR
Andrea
Comment 27 bunkem 2018-08-08 22:41:53 UTC
Hey.

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 6.2.3.2
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:

https://bz.apache.org/ooo/show_bug.cgi?id=44828

The bug is also discussed here:

https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=82706

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:

sheet\..*A.*1

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
Hi,
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.
Comment 37 Timi Agama 2021-11-19 14:47:50 UTC
I'd suggest that the following changes to the UX would solve this problem:

1. When user traces a dependent/precedent that's on another worksheet the blue square is shown, just as it is now.
2. If user hovers over the square, cursor changes to indicate that it can be clicked on.
3. When user clicks on square, a floating dialog shows up with a list of all dependents/precedents that are on other worksheets.
4. To navigate to the relevant cell, user then clicks on any item on the list.
5. When user navigates to another worksheet this way, the dependent/precendent cell is highlighted the same way as happens now.
6. User closes the floating dialog when finished.
Comment 38 documentfoundation1 2021-12-15 20:37:32 UTC
I wouldn't say it's a bug, but a missing feature - an important one, if you use complex spreadsheets...

Here is a workaround:

Just copy/paste the cell reference of the cell you wish to trace from the namebox (e.g. "Q99") and insert it (or manually) into the "find and replace" window (CTRL-H) and hit enter ("find in formulas" and "find in all sheets" should be selected automatically) - then click "find all" or "find next" etc. to jump to the cell(s) in other spreadsheets...

Maybe someone can write a macro to execute this even faster....

A full implementation in the trace-tool would be of course even better and I can't believe it is missing 8 years now ...
Comment 39 Martin Patsov 2022-01-24 22:22:55 UTC
(In reply to documentfoundation1 from comment #38)
> I wouldn't say it's a bug, but a missing feature - an important one, if you
> use complex spreadsheets...
> 
> Here is a workaround:
> 
> Just copy/paste the cell reference of the cell you wish to trace from the
> namebox (e.g. "Q99") and insert it (or manually) into the "find and replace"
> window (CTRL-H) and hit enter ("find in formulas" and "find in all sheets"
> should be selected automatically) - then click "find all" or "find next"
> etc. to jump to the cell(s) in other spreadsheets...
> 
> Maybe someone can write a macro to execute this even faster....
> 
> A full implementation in the trace-tool would be of course even better and I
> can't believe it is missing 8 years now ...

Thanks for the workaround!
Comment 40 m_a_riosv 2022-07-07 09:32:41 UTC
*** Bug 149891 has been marked as a duplicate of this bug. ***
Comment 41 Mike Kaganski 2022-07-07 09:59:57 UTC
The code pointer is at comment 13 (thank you Julien). The bFromOtherTab function argument could be changed to actual tab number, and then used instead of the call to 'maStart.SetInvalid()'. One should also take care of cases when the target is on a different document - in which case, it may be treated as it is now, to simplify the initial fix, and then it could *possibly* be changed somehow later.

Also saving/restoring should be considered: see table:detective element in ODF [1]. Its table:highlighted-range child is already written to refer to sheet like 'table:cell-range-address="Sheet1.A1:Sheet1.A1"'; however, it's unclear how conformant that is, given that documentation talks about cellRangeAddress type in this case. Regina: could you please clarify if this is an existing bug, or are we allowed to use sheet name here?

[1] https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part3-schema.html#element-table_detective
Comment 42 ady 2023-01-29 12:29:01 UTC
I'd like to present a simplified case demonstrating the need for this feature.

A colleague has maintained a (Calc's) workbook file with multiple worksheets for some time. He retired (and we have no contact anymore) and we are now in charge of it. (The case could also be that we receive some spreadsheet workbook file from some third party.) The main point is that we don't know the original logic of its workflow.

After some time, circumstances change and we need to modify it. (The case could be that some third party already modify it and the original workflow is now "broken", and we are now in charge/need of "fixing" it, starting "somewhere".)

Imagine that in column "Sheet1.D" (or "Sheet1.D:D" if you'd like) we would like to preform some changes; among others, inserting new columns (or some third party already modified it in some way, and we don't know where exactly the modification was performed that "broke" the original flow of information within the workbook).

We use "Tools > Detective > Trace Dependents" to see what would be affected, and the result shows (by a (blue) square and arrow) that there is "something" in "some place other than the same "Sheet1" worksheet of the same file that has some relation to it; we just have no idea what that "something" is.

Now we use "Edit > Find and Replace", set the check box to search in "All sheets", "Search in: Formulas" and search for "Sheet1.D". There is nothing found, so we now search for "Sheet1.$D". Nothing found either. So we go ahead and modify "Sheet1" (or someone else already did, and we have no idea what that was), but the result is that the workbook doesn't "work" anymore – not as we expected. There is no option but to analyze the entire workbook with its many worksheets in order to hunt for whatever "broke" it.

It turns out that in some other worksheet (for simplicity, let's say it's "Sheet2"), there is a formula such as:

=VLOOKUP($B11,$Sheet1.$B$11:$M$99,3)

or:

=VLOOKUP($B11;$Sheet1.$B$11:$M$99;3)

That "3" means that the above formula depends on "Sheet1.D". Moreover, the whole construction of columns B to M in Sheet1 should be considered, and cannot just be simply modified independently.

Searching (by means of CTRL-H) for anything like "Sheet1.C" to "Sheet1.N", and/or anything like "Sheet1.$C" to "Sheet1.$N" would be useless, and yet there are clear dependencies in this very simplified example.

How much time we, users, have to invest in the _partial_ workaround of using CTRL-H, instead of having "Trace > Dependents" (and "Trace > Precedents" and other "Detective" features) working on additional places other than the current worksheet only? In my experience, the answer is "too much" (and clearly many, many others agree). And then, much more additional time is wasted when the lengthy partial workaround is not nearly enough (as the aforementioned simplified example shows).

This is a simplified example; it can be much more complicated and time-consuming to analyze a multiple-sheet workbook when we don't have the "tracing" (or "Detective") feature working on multiple sheets. I even tend to limit myself to using one worksheet only (with multiple hidden areas and what not) just because of this limitation in the "Detective" tools, while I'd rather be able to effectively use more than one worksheet when it is worth. This RFE is much, much more than just "a nice thing to have".
Comment 43 bunkem 2023-01-30 14:30:36 UTC
Thanks everyone for your comments.  In particular, thank you @ady. That was a good example use case.  It is not usual for me to have 10+ sheets in a financial model.

Yes it is a critical missing function to make Calc a business appropriate replacement for Excel.  In the end, any half serious financial modeling will not happen in Calc since you can't detect the logic if you need to search through multiple sheets.  Well let's say that someone will build one model the find out this function is missing and that will end it's use.  Not ready for prime time.

It is a pretty silly oversight given LO's benefit is the open source nature of the file formats.  So your files can be used into the future and are not locked into a proprietary format but in the end you can't use the file because you can't figure out what you did or someone else did to create and use it.  This should be part of the base functionality of the program.

Can someone with the needed authority bump this up the list so the function can finally be added to Calc?

Thank you.
Comment 44 Vito 2024-01-02 14:55:30 UTC Comment hidden (me-too)