XL2000: "Reference Is Not Valid" Error Message When You Create PivotTable

This article has been archived. It is offered "as is" and will no longer be updated.
If you click PivotTable and PivotChart Report on the Data menu and then click Next to proceed to step 2 of the PivotTable and PivotChart Wizard, the Range reference box may contain the word "Database."

If you click Next again, you may receive the following error message:
Reference is not valid.
This problem may occur if the following conditions are true:
  • You display the data form in Microsoft Excel by clicking Formon the Data menu.

  • You create the PivotTable in the same worksheet in which you previously displayed the data form.
When you display the data form, Microsoft Excel creates an invisible name called "Database." This invisible name is automatically detected by the PivotTable and PivotChart Wizard. However, the wizard cannot correctly use this invisible name. This name is used only by the data form.
To work around this problem, select the range for the list that you wantto use in the PivotTable, and then click Next.

You can also delete the invisible name "Database". To do so, follow these steps:
  1. Select any cell in the worksheet.
  2. On the Insert menu, point to Name, and then click Define.
  3. In the Names in workbook box, type Database, and then click Add.
  4. Click Delete, and then click Close.
If you create a PivotTable, the "Database" name does not appear in step 2 ofthe wizard.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
More information
You can use the data form in Microsoft Excel to see, change, add, delete,and find records in a list or in a database. To use the data form, select any cell in a list or in a database, and then click Form on the Data menu.

Normally, if the "Database" defined name exists in a worksheet, thePivotTable Wizard correctly uses the range of cells referenced by thedefined name. However, if the name "Database" was created by the dataform, the name cannot be used by the PivotTable Wizard.
pivot tables XL2000

Article ID: 214349 - Last Review: 01/12/2015 16:19:38 - Revision: 4.0

Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kberrmsg kbpending KB214349