XL97: Natural Language Formulas Return Error (157095)

SYMPTOMS

In Microsoft Excel, if you enter a natural language formula, the following problems may occur:

You may receive the following error message:
That name is not valid.
-or-
The cell that contains the formula may contain the #NULL! error value. -or-
If you click the Edit Formula button to the left of the formula bar, part of the formula may be converted to a function.

For example, the formula "=Col Row" may be converted to "=Col ROW()."

CAUSE

These problems may occur if any of the following conditions are true:

The workbook in which you are entering the formula contains a subroutine or function with a name that is identical to one of the labels in the formula.

-or-
The workbook in which you are entering the formula contains a reference to another workbook that contains a subroutine or function with a name that is identical to one of the labels in the formula.

-or-
The workbook in which you are entering the formula contains a globally-defined name that is identical to one of the labels in the formula, or the worksheet in which you are entering the formula contains a locally-defined name that is identical to one of the labels in the formula.

-or-
One of the labels in the formula is the same as a function that is built into Microsoft Excel.

WORKAROUND

To work around these problems, enclose labels in the formulas in apostrophes ('). For example, change the following formula

=Charlie Tango

to this formula:

='Charlie' 'Tango'

If you enclose labels in apostrophes, you prevent them from conflicting with subroutines, functions, and defined names with names that are identical to the labels in the formula. This allows you to retain the subroutine names, function names, and defined names in any of the workbooks.

MORE INFORMATION

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

Example

The following is an example that demonstrates how natural language formulas work. To use this example, enter the following data:

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

For example, the following formula

=Charlie Tango

returns the value at the intersection of the "Charlie" row and the "Tango" column. In this case, the result is 11.

The following formula

=SUM(Sierra)

returns the sum of the "Sierra" column, which is 32.

Note that natural language formulas do not work correctly if certain conditions are true. For example, the following formula

=Charlie Tango

fails to work if any of the following conditions are true:

The active workbook contains a subroutine or function named "Charlie" or "Tango." If this is true, you receive the "That name is not valid" error message.

-or-
The active workbook references another workbook that contains a subroutine or function named "Charlie" or "Tango." If this is true, the formula does not work and the "That name is not valid" error message is displayed.

NOTE: To create a reference, click References on the Tools menu while in a Visual Basic module in the active workbook.

-or-
The active workbook contains a defined name called "Charlie" or "Tango." Or, such a name exists on the active worksheet. If this is true, a #NULL! error is displayed.

To prevent these problems from occurring, enclose your label names within apostrophes.