XL97: NLFs that use Restricted Labels Return #VALUE! Error

   =Alpha Delta
				

   ='Alpha' 'Delta'
				

MORE INFORMATION

When you enter a formula in Microsoft Excel 97, the formula is parsed by the program so that Microsoft Excel 97 can evaluate the formula. If part of the formula can be interpreted as a label (for example, when a table using the label exists on the worksheet) or as a function (for example, when an add-in that contains a function with that name is open) Microsoft Excel 97 assumes that the formula should use the function, not the label.

For example, if you enter the following table into a worksheet

               B1: Charlie   C1: Delta
   A2: Alpha   B2: 1         C2: 3
   A3: Bravo   B3: 2         C3: 4
				

and then enter the formula

   =Alpha Delta
				

you may receive a #VALUE! error and the formula may change to

   =Alpha DELTA
				

These problems occur if the Analysis Toolpak add-in is loaded into random access memory because the add-in contains a function called Delta. Microsoft Excel 97 assumes that you want the formula to use the Delta function in the add-in not the Delta label in the worksheet.

If the add-in that provides the function is not loaded into memory, the formula should function correctly. However, if you load the add-in, the formula may stop functioning. Use the workaround in this article to prevent this problem from occurring.

Determining Whether a Label is Restricted

To determine whether a label is restricted, use either of the following methods.

Method 1:

1. In a new worksheet, enter the following values

         A1:         B1: Charlie   C1: <Label>
         A2: Alpha   B2: 1         C2: 3
         A3: Bravo   B3: 2         C3: 4

   where <Label> is the label you want to test.
2. Enter the following formula into cell A5:

    =Alpha <Label>

   If the formula returns 3, the label you entered in C1 is not restricted. If the formula returns a #VALUE! error, and the formula in cell A5 changes to

   =Alpha <LABEL>

the label you entered is restricted.

Method 2:

1. Select an empty cell in a worksheet.
2. On the Standard toolbar, click Paste Function.
3. In the Function Category list, click All.
   
   Names that appear in the Function Name list in Proper format (only the first letter is upper case) are restricted.

The following words are restricted labels:

   Complex    Imaginary     Workday
   Convert    Lcm           Yield
   Delta      Multinomial
   Disc       Networkdays
   Duration   Quotient
   Effect     Received