XL98: "Not Enough Memory" Error Using Linking Formulas (181465)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q181465

SYMPTOMS

In Microsoft Excel 98 Macintosh Edition, when you do any of the following
  • You open a workbook that contains formulas that are linked to cells in other workbooks. -or-

  • You enter a formula that links to a cell (or cells) in another workbook (or workbooks). -or-

  • You close a workbook.
you may receive the following error message:
Not enough memory.
In addition, cells that contain formulas that are linked to other workbooks may return the #REF! error value.

CAUSE

This problem occurs if formulas in your workbook are linked to more than 16,375 unique cells in any one worksheet in a closed workbook. For a more detailed description of this limitation, see the "More Information" section in this article.

WORKAROUND

To work around this problem, use one of the following methods.

Method 1

Open the workbook that contains the cells to which the formulas are linked (the source workbook). The limitation described in this article does not apply when the formulas are linked to cells in an open workbook.

Method 2

Split the data in the source workbook into multiple worksheets, and then modify the formulas in the dependent workbook to account for the change in worksheet names. This method works because the limitation applies to each worksheet to which the formulas are linked. The limitation does not apply to the entire workbook.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Example Assumptions

For the following example and information, assume that you are using the following three workbooks:

Book1 (source workbook)
Book2 (source workbook)
Book3 (dependent workbook)

Each workbook contains three worksheets, Sheet1, Sheet2, and Sheet3. Book1 and Book2 contain values in A1:A17000 on each worksheet (cells are not blank). Book1 and Book2 are both closed.

When you use a workbook with formulas that link to other workbooks in Microsoft Excel 98 Macintosh Edition, the following rules apply:
  • In a workbook, the total of all of the formulas in the workbook must refer to less than 16,375 unique cells in any worksheet in any other closed workbook.

    For example, formulas in Book3 can link to 16,375 different cells in each worksheet in Book1 and 16,375 different cells in each worksheet in Book2.
  • After a formula successfully links to a cell in the source workbook, other formulas in the same dependent workbook can link to that cell.
  • Formulas that are linked to blank cells are not counted toward the linking limit. However, formulas that are linked to cells that contain the value 0 (zero) are counted toward the limit.
  • If the source workbook is open, the limitation does not apply. However, if the dependent workbook is still open, you may receive a "Not enough memory" message when you close the source workbook.

Examples

To see an example of the behavior, do the following:
  1. Given a workbook (Book1) that contain values in A1:A17000 on each of three worksheets, enter the following formulas in cells A1 and A2 on Sheet1 of the dependent workbook (Book3) while Book1 is closed:

    A1: =SUM([Book1]Sheet1!A1:A16000)
    A2: =SUM([Book1]Sheet1!A16001:A17000)

    When you enter the second formula, you receive the "Not enough memory" error message because Book3 contains links to more than 16,375 different cells in [Book1]Sheet1.
  2. Change the formula in cell A2 to the following:

    A2: =SUM([Book1]Sheet2!A16001:A17000)

    The error message does not appear because there are links to only 16,000 different cells in Sheet 1 of Book1 and 1,000 different cells on Sheet2 of Book1.

    -or-

    Change the formula in cell A1 to the following:

    A1: =SUM([Book1]Sheet1!A1:A16000,[Book1]Sheet3!A1:A16000)

    This formula works because the number of links to different cells in any one worksheet in Book1 is less than 16,375.
Note that if you enter the following formula on Sheet1 of Book3

D1: =[Book2]Sheet1!A1

and then fill the formula down to row 16,376, you receive the error message because Book3 contains links to more than 16,375 different cells on Sheet1 of Book2.
Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbprb KB181465
©2002 Microsoft Corporation. All rights reserved.