Microsoft KB Archive/20271

Excel 1.00: Linking Complex External Reference Causes Error PSS ID Number: Q20271 Article last modified on 04-16-1991 PSS database name: M_eXceL

1.00

MACINTOSH

Summary:

In version 1.00 of Excel, functions that use arrays for arguments cannot be used in a complex external reference. This limitation is caused by Excel’s inability to force a recalculation in supporting worksheets.

Such a recalculation can be forced only by using a macro or by placing the function on the supporting worksheet and using reference to the results of the calculations on your dependent sheet.

The functions that cause this situation are LOOKUP, MATCH, HLOOKUP, VLOOKUP, TREND, GROWTH, LINEST, and LOGEST. If any of these functions have an external reference for their array arguments, and if any of the cells in each array (or the return cell in the lookup cases) are not calculated when the function is evaluated, then an ID=02 error will result.

This problem was corrected in Version 1.03.

Copyright Microsoft Corporation 1991.