Article ID: 157483 - Last Review: October 21, 2000 - Revision: 1.0

XL97: Limit of Natural Language Formulas per Worksheet

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q157483
Expand all | Collapse all

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.

Also, when 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 some or all of the error messages shown below, and Excel may stop responding:
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.
Some of the error messages may appear more than once. Ultimately, you are forced to restart your computer.
Back to the top

CAUSE

The problems may occur if both of 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.
In Excel 97, a worksheet can contain up to 32,764 natural language formulas. If you exceed this limit, the problems described in this article may occur.
Back to the top

WORKAROUND

To work around this problem, remove some of the existing natural language formulas from the worksheet before you add any new ones.
Back to the top

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.
Back to the top

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
If you enter the above information into a new worksheet, you can find values within the table, or perform actions on parts of the table, by using a natural language formula.

For example, the following formula: 
   =Charlie Tango
will return the value 11, which is the value at the intersection of the "Charlie" row and the "Tango" column in the table.

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.
Back to the top
APPLIES TO
  • Microsoft Excel 97 Standard Edition
Back to the top
Keywords: 
kbprb KB157483
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top