XL: "Reference Not Valid" Error When You Create PivotTable

ID: Q177169

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, if you click PivotTable Report on the Data menu, and then click Next to proceed to step 2 of the PivotTable Wizard, the Range reference box may contain the word "Database."

If you click Next, you may receive the following error message:

   Reference is not valid.

CAUSE

This problem may occur if the following conditions are true:

You display the data form in Microsoft Excel by clicking Form on the Data menu.
```
    -and-
```
You create the PivotTable in the same worksheet in which you previously displayed the data form.

Specifically, when you display the data form, Microsoft Excel creates an invisible name called "Database." This invisible name is automatically detected by the PivotTable Wizard. However, the PivotTable Wizard cannot correctly use this invisible name; this name is used only by the data form.

NOTE: This problem does not occur in earlier versions of Microsoft Excel.

WORKAROUND

To work around this problem, select the range for the list that you want to use in the PivotTable, and then click Next.

Or, delete the invisible name Database by following these steps:

1. Select any cell in the worksheet.

2. Point to Name on the Insert menu, and then click Define.

3. In the Names In Workbook box, type "Database" (without the quotation

   marks). Then, click Add.

4. Click Delete. Then, click Close.

If you create a PivotTable, the Database name does not appear in step 2 of the wizard.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products 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 database. To use the data form, select any cell in a list or database, and then click Form on the Data menu.

Normally, if the "Database" defined name exists in a worksheet, the PivotTable Wizard correctly uses the range of cells referenced by the defined name. However, if "Database" the name was created by the data form, the name cannot be used by the PivotTable Wizard.

Additional query words: XL97 pivot tables

Keywords          : kberrmsg xlpivot 
Version           : WINDOWS:97; MACINTOSH:98
Platform          : MACINTOSH WINDOWS
Issue type        : kbbug

Last Reviewed: January 8, 1999