Calculating a reference to a cell on the same worksheet
is faster than calculating a reference to a cell on another
Excel 97 and 2000 maintain a sheet-level dependency tree rather
than a full dependency tree at the cell level for worksheet
cross-references. 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 inter-sheet 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 Cross-references, (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
This process continues until there are no remaining uncalculated
sheets and cells.
Forward Worksheet Cross-references (forward inter Sheet
dependencies) can cause uncalculated cells to be evaluated multiple
times and can increase your calculation times significantly.
When these forward worksheet cross-references
become linked together into one or more circular worksheet cross-reference
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 cross-references.
You can also analyse the worksheet cross-references by using
the FastExcel Map
CrossReferences command. This will give you a recommended
worksheet calculation sequence, and show you the circular worksheet
cross-reference paths it has found.
Excel 2002/2003 has a significantly improved calculation
algorithm for handling forward worksheet cross-references.
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.