XL2000: Natural Language Formulas Return Error

ID: Q199411

The information in this article applies to:

Microsoft Excel 2000

SYMPTOMS

In Microsoft Excel, if you enter a natural language formula (NLF), you may receive the following error message

That name is not valid.

or the cell containing 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 into 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 when you have the Accept labels in formulas option selected:

The workbook into which you are entering the formula contains a subroutine or function whose name is identical to one of the labels within your formula.

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

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

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

WORKAROUND

To prevent these problems from occurring, enclose labels used in your formulas within apostrophes ('). For example, instead of this formula


   =Charlie Tango

use this formula:


   ='Charlie' 'Tango'

Enclosing labels within apostrophes prevents them from conflicting with subroutines, functions, and defined names whose names are identical to the label(s) within your formula. This allows you to retain your subroutine names, function names, and defined names in any of your 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.

NOTE: Unlike the earlier version, natural language formulas are disabled by default in Excel 2000. Follow these steps to enable the Accept labels in formulas option in the current workbook.

On the Tools menu, click the Calculation tab.

Click to select the Accept labels in formulas check box, and then click OK.

Below is an example that 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

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

Or, you could enter this formula:


   =SUM(Sierra)

to get the sum of the Sierra-column, 32.

However, natural language formulas will not work correctly if certain conditions are true. For example, if you have the following formula:


   =Charlie Tango

The formula will fail to work if any of the following conditions are true:

If the active workbook contains a subroutine or function named "Charlie" or "Tango", the formula will not work. You will receive the "That name is not valid" error message.

-or-
If the active workbook references another workbook that contains a subroutine or function named "Charlie" or "Tango", the formula will not work. The same error message will be displayed. (To create a reference, click References on the Tools menu while in a Visual Basic module in the active workbook.)

-or-
If the active workbook contains a defined name called "Charlie" or "Tango", or if such a name exists on the active worksheet, the formula will not work. A #NULL! error message will be displayed.

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

Additional query words: XL2000 natural-language elf

Keywords : kbdta xlui xlvbainfo xlformula
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb