|
Map Worksheet Cross-references.
.png) Map Worksheet Cross-references produces a tabular map showing how the references between worksheets.
Worksheet Cross-references occur when a formula on one worksheet refers to another worksheet.
.png)
Optionally you can also request that the worksheets are re-sequenced to minimize the number of worksheet cross-references: but note that with current Excel versions this will generally have little or no impact on calculation times.
Worksheet Calculation Sequence Forward Cross-reference Tables.
The output is one or two tables of worksheet cross-reference counts, with forward cross-references highlighted in orange:
The first table shows forward cross-reference counts using the Current Worksheet Calculation Sequence.
.png)
The second table shows the Optimized Worksheet Calculation Sequence, and its effect on the forward cross-reference counts.
.png)
Worksheet Forward Cross-reference Tables: Sheet Refers-To and Referenced-By:
|
Rows show Refers-To counts
|
Reading a row across the table shows you the refers-To counts.
|
|
Columns show Referenced-By counts
|
Worksheet Forward Cross-reference Tables: Color-Coding:
|
Orange cells show problem areas
|
Orange highlighted cells show forward worksheet references. These are the most common cause of excessive workbook overhead.
|
Bright green cells on the diagonal are empty because FastExcel does not count references within a sheet.
Light green columns show worksheets that are not referenced-by any other sheet. These are typically report sheets: for example, the Summary sheet.
Light green rows show worksheets that do not refers-to any other sheet. These are typically sheets containing the input data.
Using the optimized Sheet calculation sequence:
|
Changing the worksheet names changes the worksheet calculation sequence
|
If FastExcel has significantly reduced the forward references you will be asked if you want to automatically change the sheet names.
NOTE: In Excel97 and Excel2000 Excel’s worksheet calculation sequence is the alphabetically sorted worksheet name sequence rather than the sequence of the worksheets in the workbook.
In Excel 2002 and later versions the worksheet calculation sequence is generally not important to calculation time.
|
Circular Worksheet Cross-reference Paths table
If FastExcel can’t find a worksheet calculation sequence with zero forward cross-references it’s probably because the workbook contains circular cross-reference paths.
.png)
The Path Length column shows the number of worksheets involved in the circular path.
In complex workbooks there may be thousands of unique circular paths. FastExcel is designed show you the shorter paths first, and will not attempt to find paths containing more than 6 sheets.
|
