Making life harder
The example we used to introduce OAK's formula reconstruction capabilities made reference to cells that were on the same worksheet as itself. The result was a reconstruction that, while somewhat useful, did not achieve anything that could not be achieved using the mechanisms built into Excel for determining a cell's precedents.
The OAK formula reconstructer becomes more powerful when applied to formulas that mention several worksheets.
Example
| 1 | Create a new workbook. |
| 2 | If the workbook has fewer than four worksheets, add some more, using the Insert | Worksheet command. |
| 3 | In cell Sheet1!E3000, type the number 10 |
| 4 | In cell Sheet2!E6000, type the number 20 |
| 5 | In cell Sheet3!E9000, type the number 30. |
| 6 | In cell Sheet4!F9, type the formula =Sheet1!E3000+Sheet2!E6000+Sheet3!E9000. You should see the value 60. |
| 7 | With cell Sheet4!F9 still selected, invoke OAK's Formula | Reconstruct command. |
| 8 | A dialog will appear, with lots of options. These will be explained later in this help. For now, accept the defaults, and press OK. |
OAK will generate a new workbook, like the one below.
interpreting the reconstruction
This workbook is laid out exactly the same as the example given for the reconstruction a simple, single-worksheet formula, except that the fact that the three precedent are sited on different worksheets is indicated by the blue banded labels in rows 8, 12 and 16.
The original formula, =Sheet1!E3000+Sheet2!E6000+Sheet3!E9000, involved cells that were thousands of rows apart, and on different worksheets, which means that the facilities for examining a formula's precedents do not work well.
The reconstruction restates the calculation so that it involves cells that are just a few rows further down the worksheet, and gathered together on the same worksheet, which means that they can be examined much more easily.