Timelines in reconstructions |
This page in 2007 Top Previous Next |
|
When neighboring cells in a formula are consistent, that is, they are copies of each other, it is generally the case that they are arrayed along some dimension. Most commonly that dimension is time, though it does not have to be. When there is such a dimension involved, it is often set out along the top of the spreadsheet, in the form of a timeline or other enumeration. Excel recognizes the usefulness of this material in defining what the different calculations are about by providing the ability to •freeze a few rows at the top of the window (and columns down its side) in which the worksheet is being displayed, so that these stay still when the rest of the worksheet is scrolled •to define print titles which cause similar repetition of selected rows (and columns) when the worksheet is printed. OAK similarly recognizes that these items are valuable in interpreting a calculation, and provides the means to include them in a reconstruction. The dialog that appears when the Formula | Reconstruct command is invoked provides the means to specify •that any rows frozen in the current window should be reproduced at the top of the reconstruction •that any rows specified in the worksheet's Print titles should be reproduced at the top of the reconstruction •no extra rows are wanted at the top of the reconstruction (the default).
Though the frozen panes of a window and print titles of a worksheet can specify both rows and columns, only the row part of the definition is used by OAK. The column part is ignored. (That is why the references to columns are parenthesized above.) Since these rows are not essential to the reconstructed calculation, but help in interpreting it, they are like the context rows that OAK can also include, and are colored the same grey as those rows. Like those context rows, they are not values, but formulas, which link back to the original spreadsheet and can be followed with Ctrl+[ (left square bracket). Occasionally a timeline does actually participate in a reconstructed calculation. In such a case it will appear below the reconstructed formula, alongside any other links to precedent cells, as well as along the top as context. The timeline that is reproduced in a reconstruction report is the one the pertains to the worksheet bearing the formula being reconstructed. The hierarchy of precedents includes links to other worksheets. There is no guarantee that those worksheets associate columns with time periods in the same way, so the labeling implied by the report timeline may not be consistent with any cells that originate from them. Tip: In general it is strongly recommended that worksheets are consistently aligned as to timelines. Writing formulas in terms of meaningful names, which rely on Excel's automatic row and column matching mechanisms, is a good way to enforce this alignment. |