Using the Calc Detective

When you create formulas and functions for your spreadsheets it is essential that you reference the correct cells in them. No matter how good a formula or how well chosen a function, you won't get the correct answer if the formula is not looking at the appropriate values on the Calc sheet.

A tool that will help you troubleshoot your formulas (and your functions) is the Detective. The Detective allows you to view two entities related to formulas. You can have Calc display the cells that are referenced by a particular formula. These cells containing data are called precedents. Calc can also show you the cells (those containing formulas) that rely on a cell for a particular value. Cells containing formulas or functions are referred to as dependents, meaning they need to be supplied data from other cells on the sheet.

To Trace Precedents

The Detective will highlight the cells that supply data to a particular formula.

1.	Click on the cell that holds the formula (or function).
2.	Select Tools, then point at Detective.
3.	Select Trace Precedents.

The precedents for the formula (or function) will be marked with a blue line that traces back to the formula. Figure 5.13 shows the precedents for a simple multiplication formula.

If you place the mouse pointer on one of the precedents, the mouse pointer becomes a magnifying glass containing an arrow. You can use this pointer to move up and down the list of precedents for the formula. Just double-click on one of the precedent cells and it will move you to the next one in line (this is useful for complex formulas and functions that reference a number of data cells).

You can also trace the dependents for more than one cell holding a formula using the Fill Mode supplied by the Detective.

1.	Select Tools, then point at Detective.
2.	Select Fill Mode on the Detective submenu.
3.	The mouse pointer becomes a Fill tool. Click on any number of formulas using the Detective's Fill tool to trace their precedents.
4.	When you have finished working with the Fill tool, select Tools, point at Detective, and then select Fill Mode to turn the feature off.

To remove the precedents shown on the spreadsheet, select Tools, point at Detective, and then select Remove Precedents. This removes the current precedent from the sheet. To remove all trace precedents, select Remove All Traces on the Detective submenu.

To Trace Dependents

You can also trace which formulas (or functions) depend on a particular cell for data.

1.	Click on the cell that holds the data.
2.	Select Tools, then point at Detective.
3.	Select Trace Dependents.

A blue line will be drawn from the current cell to any formulas that depend on it for data. In cases where the current cell is also part of a range of cells used by a formula or function, a blue box will be drawn around the entire range of cells. Figure 5.14 shows the dependent tracings for two different cells. In the case of the Totals column, all the cells in that column are part of a range of cells that supplies data to cell D15.

To Trace Errors

The Detective also supplies you with the ability to trace the cells that are causing errors in a particular formula.

1.	Click on the cell that holds the formula (or function) containing the error message.
2.	Select Tools, then point at Detective.
3.	Select Trace Error.

The cells that are providing data to the formula (or function) with the error message will be traced back to the formula. In Figure 5.15, a formula is shown with an error message. After error tracing, note that the formula is showing the error message because cell F13 has been referenced in the formula rather than cell F12 (where the value needed actually resides).

While the Detective provides limited assistance in searching out problems related to formulas and functions, other tools exist. Error messages can often provide clues related to a formula's or function's problem. And functions can be analyzed with the Function AutoPilot. Both error messages and the Function AutoPilot are discussed in the next chapter.