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.

Created attachment 89974 [details]
Image of Spreadsheet entry box showing cell trace location

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.

I confirm that the "different sheet trace" is not clickable in 4.0.5.2 either.

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?

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.

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)

(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

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

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

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.

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.

*** Bug 65786 has been marked as a duplicate of this bug. ***

Created attachment 120285 [details]
example ods showing the problem

still present in current master branch 
(commit ffde331fbefec360d0f6ca5268cdc9acd1a6440b)

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

NOTE: patch is too lame, doesn't work on multiple references either...

(In reply to QA Administrators from comment #21)

The bug is still present (LibreOffice 5.2.3.3, 32-bit version, Windows 10).

*** Bug 104124 has been marked as a duplicate of this bug. ***

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.

Issue still present in Libreoffice Calc 6.0.4.2 (x64)
BR
Andrea

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.

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

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.

+1 for this being important functionality. Encountered on 6.2.3.2

+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???

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)

+1 for this one as well. This is keeping me from making anything more than simple workbooks in Calc.

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.

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.

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.

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.

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

(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!

*** Bug 149891 has been marked as a duplicate of this bug. ***

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

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

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.