Complex External Reference Not Calculating Correctly

 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.

REFERENCES

"Microsoft Excel for Windows Reference Guide." Version 2.1x, pages 465-477.

"Microsoft Excel User's Guide." Version 3.00, pages 307-314.

KBCategory: kbother
KBSubcategory:

Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.