Naming Spreadsheet Areas
Calc lets you name sections of your spreadsheet to make those areas more meaningful. For example, you can select a column of first quarter sales numbers and name that area "Q1Sales".
This is a useful feature for a couple of reasons. When you name areas in Calc, you can jump to those areas quickly in the spreadsheet, either from the Navigator window or from the drop-down list in the formula bar, as shown in Figure 22-11. You can also include names in formulas rather than cell ranges, making formulas more meaningful. The following example illustrates this:
=SUM(B6:B9) can be written as =SUM(Q1Sales)
Figure 22-11. Jumping to named areas of a spreadsheet
You can name ranges individually, or select multiple rows and columns and create multiple named areas automatically with one procedure.
Naming a Single Region
If you want to name more than one region, you can keep the Define Names window open by clicking Add instead of OK in step 3. You can then enter another name, click the Shrink button to select a new region in the spreadsheet, click Shrink again, and click Add.
Naming Multiple Regions Automatically
You can select a big area of a spreadsheet that contains multiple regions you'd like to name, then use this procedure to create multiple named areas out of the big area. When you use this procedure, Calc creates names for regions based on column and/or row headings in the selected area. An example is shown in Figure 22-13, followed by steps.
Figure 22-13. Creating multiple named regions with one procedure
Deleting Area Names
You can delete the name assignments you've given to areas. Deleting names doesn't affect the data areas they were assigned to. However, if you delete the names of areas you've used in formulas (such as =sum(Q1) ), the formulas will no longer work. Also, if you delete a name that was used to create a hyperlink to the area by dragging from Navigator, the hyperlink will no longer work.
Pointing to Cell References and Errors
Cells can sometimes contain references to a lot of other cells , especially when you use more complex formulas and functions. Also, a single cell can be referenced in a lot of other cells. Calc has a couple of great tools that draw arrows to or from a selected cell, pointing to the other cells that the current cell references, called precedents , or the cells that reference the current cell, called dependents .
Tracing precedents and dependents are great for troubleshooting, because they show you exactly which cells are used in a calculation, making it easier to spot incorrect cell references.
Calc also includes an error tracing tool, which points to the cells causing an error in a particular cell.
Arrows appear, pointing to the relevant references, as illustrated in Figure 22-14.
Figure 22-14. Tracing precedents, dependents, and errors
You can remove the arrows by choosing Tools > Detective, and selecting the relevant remove item. If you're going to use traces on a regular basis, consider assigning a shortcut key removing traces. See Assigning Shortcut Keys to Menu Items on page 104. Select the Options category, then select the Remove All Traces command.