When you use a natural language formula to reference cells, the result is a
#NAME? error.
This problem occurs when the data the formula refers to is returned from
either of the following:
- a link
-or-
- an external database
If a formula that uses a natural language reference returns a
#NAME? error
after you update data from an external database, do the following to
recalculate the formula:
- Click the cell that contains the #NAME? error.
- Press F2, and then press ENTER.
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article.
When you use natural language formulas, it is not necessary to create
defined names or determine the cell ranges in advance. Microsoft Excel 97
determines the range based on the row and column labels that you provide
in the table on your worksheet.
You can link data to a Microsoft Excel workbook in the following ways:
- Use a dynamic data exchange (DDE) link from a different program, such
as Microsoft Word or Microsoft Powerpoint.
- Create a link from one workbook to another.
- Create a linked reference from one cell to another within the same
workbook.
In Microsoft Excel, you can access data from an external database in the
following ways:
- Use the Microsoft Query add-in to return data to a worksheet.
- Create a PivotTable that uses external data.
- Use the Open Database Connectivity (ODBC) add-in in a macro.
Example
To set up an example that demonstrates the problem, type the following data
in a worksheet:
A1: B1: Quarter1 C1: Quarter2
A2: North B2: 100 C2: 120
A3: West B3: 110 C3: 130
A4: East B4: 120 C4: 140
A5: South B6: 130 C5: 150
In this example, the following formula returns the value 240 for the sum
of quarters 1 and 2 for the West region:
However, if your data is linked or from an external data source and you
update the data, the formula returns
#NAME?.
For additional information about using natural language formulas, please
see the following article in the Microsoft Knowledge Base:
161881Â
(http://kbalertz.com/Feedback.aspx?kbNumber=161881/EN-US/
)
XL97: How to Use Natural Language Formulas
For more information about natural language formulas, click the Index tab
in Help, type the following text
labels, in formulas
and then double-click the selected text to go to the "Learn about labels
and names in formulas" topic.