Microsoft KB Archive/70233

{| = Complex External Reference Not Calculating Correctly =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q70233

SUMMARY
Some complex external references in Excel 2.x may not work correctly when used in Excel 3.00. For example, an external reference similar to =SUM(SHEET1.XLS!$A$1:SHEET1.XLS!$A$3) will calculate correctly if SHEET1.XLS is open. However if SHEET1.XLS is not open, then the formula does not produce the expected results.

MORE INFORMATION
Consider the above SUM formula and the following data in SHEET1.XLS.

A1: 1 A2: 2 A3: 3 When SHEET1.XLS is open, the sum is SIX. However, if SHEET1.XLS is closed, then the sum is FOUR. The formula works correctly in Excel 2.x because it is a complex external reference that requires the supporting sheets to be open for the references to be updated. However, in Excel 3.00, supporting sheets do not need to be open. In the above example, Excel 3.00 is calculating the formula as if it were were written:

=SUM(SHEET1.XLS!$A$1,SHEET1.XLS!$A$3) Workaround: In this specific example, you can use the formula: =SUM(SHEET1.XLS!$A$1:$A$3) to obtain the correct value with SHEET1.XLS closed.