Microsoft KB Archive/125641

= Microsoft Knowledge Base =

MXL5: PivotTable Uses Incorrect Range If Selected with Mouse
Last reviewed: September 12, 1996

Article ID: Q125641

The information in this article applies to:


 * 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 Cannot open PivotTable source file ' ' 

These problems occur only when you create a PivotTable from data in a Microsoft Excel list or database.

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 Method 2: Use the Browse button by doing the following:

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 The reference to the data appears in the Range box in the Step 2 dialog box without the workbook name.

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

Cannot open PivotTable source file ' ' The reference to the data appears in the Range box in the Step 2 dialog box without the workbook name.