Using the Detective Feature in OpenOffice Calc

I do enjoy having software do work for me.

And a feature that will do that, but which I rarely hear anyone talking about, is the Detective under the Tools menu.

The Detective has many features; I'll go over them in a few different posts. Here's how to get started: tracing the precedents (the ingredients that go into a cell) and the dependents (the cells that look back to a cell as the basis for the calculations).

The Sample Spreadsheet
In this spreadsheet, the expenses for the spring and summer are totaled, and the fall and winter are totaled. We also have a total for the whole year. So the spring and summer depend on the original numbers in the columns, as do the fall and winter totals, and of course the totals at the bottom of the columns.

In addition, there are two calculations that figure out the percentage, which go back two levels.

Click the following picture to see a bigger version; this is definitely too small to see reasonably.

Precedents
I want to see what cells are involved in the calculation for the Spring and Summer percentage. So I click on that cell, and choose Tools > Detective > Trace Precedents. The blue arrows show me the two cells that are referenced in the cell.

Dependents
Let's say I'm thinking of changing the total for the Fall and Winter Expenses but I don't want to screw up other calculations. I can see a cell's dependents by selecting the cell and choosing Tools > Detective > Trace Dependents.

Those are two of the most useful basics for the Detective. I'll talk more about the other features in future blogs.

---

Comments

[油缸]

油缸
气缸
液压缸

[Fry]

When a dependent is on another sheet, trace shows a line connected to a diamond. I can't seem to click on it. How can I follow the trace to the other sheet?

[Aman Gopal Sureka]

does trace precedents / dependents also work on values linked from different workbooks / different files ? In MS-Excel it opens the linked file, but in OpenOffice I see a blue box ended arrow, instead of a regular arrow.

sincerely appreciate your help and information on workarounds / macro extension or support if any.

thank you,

aman

[Solveig]

Hi Aman,

It sure doesn't seem to be very useful for references that aren't in the same sheet. And the box at the end of the arrow gives no clues, basically just "this came from another sheet" is all the info you get.

That would be a lovely feature for the next release! (And I tested with 3.0 so no hope there.)

I don't know of any extensions now but extensions.openoffice.org seems to have new stuff added every day.

[Haley Jenkins]

Hi,
The company I work for create and market computer workbooks for students, which I am now converting from Microsoft Vista to Open Office.
I have a Microsoft spreadsheet with two values showing as #VALUE! however when it is converted to OOo, it is simply shownig as $0.00. So when I try to apply trace errors it is not picked up.
What is the error messgae OOo should say in a cell?
Any and all help appreciated!
Also, I have searched for a Calc manual, but cannot find one, is there one available?

Thank you