XL97: "Not Enough Memory" Error Using Linking Formulas

ID: Q167079

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, 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.

MORE INFORMATION

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

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

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

When you use a workbook with formulas that link to other workbooks in Microsoft Excel 97, 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.xls can link to 16,375 different cells in each worksheet in Book1.xls and 16,375 different cells in each worksheet in Book2.xls.
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.xls) 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.xls):

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

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

2. Change the formula in cell A2 to the following:

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

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

   -or-

   You could also change the formula in cell A1 to the following:

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

   This formula works because the number of links to different cells in any
   one worksheet in Book1.xls is less than 16,375.

Note that if you enter the following formula on Sheet1 of Book3.xls

   D1: =[Book2.xls]Sheet1!A1

and then fill the formula down to row 16,376, you receive the error message because Book3.xls contains links to more than 16,375 different cells on Sheet1 of Book2.xls.

Additional query words: 97 XL97 16376 16375 16384

Keywords          : xlloadsave xlformula 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: November 1, 1998