Microsoft Knowledge Base

• Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

When you create a PivotTable in Microsoft Excel, if you use the mouse to select a range in a workbook other than the active workbook (the workbook that was active when you inserted the PivotTable), one of the following symptoms may occur when you choose either Next or Finish in the PivotTable Wizard-Step 2 of 4 dialog box:

• The PivotTable is created from incorrect data

  -or-

• You receive the following error message:

        Pivot Table field name is not valid
  

  -or-

• You receive the following error messages:

        Cannot find <filename>
  

        Cannot open PivotTable source file '<filename>'
  

CAUSE

This behavior only occurs when you choose a workbook from the Window menu and select a range on a worksheet in this workbook to use as data in the PivotTable while the PivotTable Wizard dialog box is open. When you select a range in a non-active workbook in Step 2 of the PivotTable Wizard, the workbook name is not included in the reference for the list. This forces the PivotTable Wizard to look for the range on a worksheet in the active workbook, even though the range was not originally selected from the active workbook.

RESOLUTION

To work around this behavior, do not use the Window menu to select an inactive source document for PivotTable data in the PivotTable Wizard - Step 2 of 4 dialog box. Instead, use any of following methods.

Method 1: Enter the complete reference to the PivotTable data in the Range

          box in the PivotTable Wizard - Step 2 of 4 dialog box in the
          following form:

             '[WorkbookName]WorksheetName'!Range

          For example, if the non-active workbook is named "Year 1995"
          (without the quotation marks), and the worksheet is named
          "Quarter 1" (without the quotation marks), and PivotTable data is
          located in the range A1:C100, enter the following reference in
          the Range box:

             '[Year 1995]Quarter 1'!$A$1:$C$100

          a. In the PivotTable Wizard - Step 2 of 4 dialog box, choose the
             Browse button. Select the file that contains the PivotTable
             data.

          b. In the Browse dialog box, type the sheet name and cell
             reference for the data, or activate the source document by
             choosing the file from the Window menu, and selecting the
             source range.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel version 5.0 for the Macintosh. This problem was corrected in Microsoft Excel version 5.0a for the Macintosh.

MORE INFORMATION

There are three known possible scenarios where the PivotTable Wizard may behave incorrectly.

Scenario 1: Same Worksheet Names and a Valid Data List

If the active workbook contains a worksheet with the same name as the non- active workbook worksheet (the one that contains your data list) and the same range on the worksheet of the active workbook also contains a valid data list, you will not receive an error message. However, the PivotTable will be created from the data on the worksheet in the active workbook, instead of the data you selected on the worksheet in the external workbook.

Scenario 2: Same Worksheet Names and An Invalid Data List

If a worksheet in the active workbook has the same name as the worksheet in the non-active workbook (the one that contains the list of data you want to use), but the range on the worksheet of the active workbook does not contain a valid data list, you receive the following error message after Step 2 of the PivotTable Wizard:

   PivotTable field name is not valid

Scenario 3: Different Worksheet Names

If the active workbook does not contain a worksheet with the same name as the worksheet in the non-active workbook that contains your PivotTable data, you receive the following error message after Step 2 of the PivotTable Wizard:

   Cannot find <filename>

   Cannot open PivotTable source file '<filename>'

REFERENCES

"Microsoft Excel User's Guide," version 5.0, Chapter 24, page 162

For more information on Entering An External Reference In A Formula, choose the Search button in Help and type:

   references, external