XL98: Natural Language Formulas Return Error

• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

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

WORKAROUND

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

   =Charlie Tango

   ='Charlie' 'Tango'

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.

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

For example, the following formula

   =Charlie Tango

The following formula

   =SUM(Sierra)

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

   =Charlie Tango

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