XL97: "Not Enough Memory" Error Using Linking Formulas

• 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.

   Not enough memory.

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

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)

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.

   D1: =[Book2.xls]Sheet1!A1