XL97: Limit of Natural Language Formulas per Worksheet

- Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, when you enter a formula in a cell, the formula may disappear unexpectedly, leaving the cell empty. You do not receive an error message.

Or, if you fill a range with formulas, you receive some (or all) of the following error messages:

   Microsoft Excel cannot calculate a formula. There is a circular
   reference in an open workbook, but the references that cause it
   cannot be listed for you. Try editing the last formula you entered
   or removing it with the Undo command (Edit menu).

   -or-

   A fatal exception 0E has occurred at 0137:BFF9A07C.

   -or-

   EXCEL caused an invalid page fault in module KERNEL32.DLL at
   0137:bff858e9.

   -or-

   EXPLORER caused a general protection fault in module KRNL386.EXE at
   0001:00007511.

CAUSE

The problems may occur if the following conditions are true:

• You are using natural language formulas. For example:

        =Value1 Value2
  

  where Value1 and Value2 are labels on a table of information within the worksheet.

      -and-
  

• The worksheet contains a very large number of formulas of this type.

RESOLUTION

In Microsoft Excel 97, a worksheet can contain up to 32764 natural language formulas. Once this limit is reached, you cannot add any more natural language formulas without first removing others.

Also, if you attempt to add a large number of natural language formulas all at once (for example, by filling a formula down through a large number of cells), you may receive the error messages shown above, and Microsoft Excel may stop responding.

STATUS

The limitation of 32764 natural language formulas per worksheet is by design of Microsoft Excel 97.

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

In Microsoft Excel 97, natural language formulas allow you to refer to values in tables of information without having to define names or use bulky INDEX-MATCH style formulas. The following example demonstrates how natural language formulas work:

   A1:           B1: Romeo   C1: Sierra   D1: Tango   E1: Uniform
   A2: Alpha     B2: 1       C2: 2        D2: 3       E2: 4
   A3: Bravo     B3: 5       C3: 6        D3: 7       E3: 8
   A4: Charlie   B4: 9       C4: 10       D4: 11      E4: 12
   A5: Echo      B5: 13      C5: 14       D5: 15      E5: 16

For example, the following formula:

   =Charlie Tango

There is a limit of 32764 natural language formulas per worksheet in Microsoft Excel 97. Once this limit is reached, if you try to enter one more natural language formula, the formula is not accepted by Microsoft Excel. If you attempt to add a large number of natural language formulas all at once, Microsoft Excel may stop responding.