Calculated column returns #NAME? error when column headings are used in formula in Excel for Mac (272882)


The information in this article applies to:

  • Microsoft Excel 2004 for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel X for Mac
This article was previously published under Q272882

SYMPTOMS

When you use column heading names in the formula for a calculated column using the List feature, the cells in the calculated column may display the following error message
#NAME?
and the formula is not calculated.

CAUSE

This behavior occurs under one of the following conditions:
  • You insert a list and then use the column headings in natural language formulas in a calculated column.

    -or-
  • You convert a list column that contains natural language formulas to a calculated column.

WORKAROUND

To work around this issue, create names for the list columns that Microsoft Excel for Mac can use in formulas. To do so, follow these steps:
  1. Click the column heading and drag the pointer to select all the cells in the column, including the cell in the new row at the bottom of the list.

    You can do this for more than one column at a time.
  2. On the Insert menu, point to Name, and then click Create.
  3. In the Create Names dialog box, click to select Top row, and then click OK.

    This assigns the column headings as names for the list column, including cells in the new row. As the list expands, the Name definition is updated to include the new rows. These names can be used in formulas in a calculated column.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

For more information about how to create and use lists, click the Office Assistant, type guidelines for creating a list by using the list manager, click Search, and then click to view the topic.

For more information about the #NAME? error, click the Office Assistant, type what does the error #NAME? mean?, click Search, and then click to view the topic.

For more information about naming selections of cells, click the Office Assistant, type Name cells in a workbook, click Search, and then click a topic to view it.

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar.
Modification Type:MajorLast Reviewed:2/9/2005
Keywords:kbpending kbprb KB272882
©2004 Microsoft Corporation. All rights reserved.