Reconstructing multi-sheet formulas

Top  Previous  Next

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

1Create a new workbook.
2If the workbook has fewer than four worksheets, add some more, using the Home | Cells | Insert | Insert sheet command.
3In cell Sheet1!E3000, type the number 10
4In cell Sheet2!E6000, type the number 20
5In cell Sheet3!E9000, type the number 30.
6In cell Sheet4!F9, type the formula  =Sheet1!E3000+Sheet2!E6000+Sheet3!E9000.  You should see the value 60.
7With cell Sheet4!F9 still selected, invoke the OAK | Formulas | Reconstruct command.  
8A 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.

Reconstruction example 2

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.