Calculating a reference to a cell on the same worksheet
is faster than calculating a reference to a cell on another
worksheet.
Excel 97 and 2000 maintain a sheetlevel dependency tree rather
than a full dependency tree at the cell level for worksheet
crossreferences. Worksheets will be correctly flagged as containing
uncalculated cells when they contain formulae dependent on an
uncalculated cell/name on another worksheet.
Excel 97 and 2000 handle intersheet dependencies by
calculating each sheet containing uncalculated cells in
turn, in the sequence given by their alphabetic names.
The calculation process for each sheet only looks at cells
that are currently flagged as uncalculated.
If each sheet only refers backwards then the process is
complete after each sheet has been calculated once, and
all cells and sheets will have been flagged as calculated.
If any sheets contain cells with uncalculated Forward
Worksheet Crossreferences, (references to uncalculated
formulae on worksheets that are calculated after the current
worksheet), these cells cannot yet be calculated and so
the worksheet will still be flagged as containing uncalculated
cells.
This process continues until there are no remaining uncalculated
sheets and cells.
Forward Worksheet Crossreferences (forward inter Sheet
dependencies) can cause uncalculated cells to be evaluated multiple
times and can increase your calculation times significantly.
When these forward worksheet crossreferences
become linked together into one or more circular worksheet crossreference
paths calculation time can increase even more.
You can detect this effect with FastExcel
by looking at the Workbook Overhead: this shows the amount of
time due to forward worksheet crossreferences.
You can also analyse the worksheet crossreferences by using
the FastExcel Map
CrossReferences command. This will give you a recommended
worksheet calculation sequence, and show you the circular worksheet
crossreference paths it has found.
Excel 2002/2003 has a significantly improved calculation
algorithm for handling forward worksheet crossreferences.
Excel2002/2003 also calculates worksheets in alphabetic
sequence but develops a single dependency chain sequence for
each workbook. For complex workbooks containing many forward
worksheet references and/or circular worksheet references using
Excel 2002/2003 can significantly speed up calculation times.
