Excel - Pivot Table - Reference is not valid

Asked By Sinner on 31-Mar-07 10:18 AM
I have a list in a file name abc.xls. I have defined the list with a
name say XYZ.
I have another file Summary.xls with a pivot table that I'm using to
give me a summary of that list.
The 'XYZ' which is the defined name of the actual list in abc.xls file
works when both the files are open.

When I open the Summary.xls file, first message says "Reference is not
valid" and second message says "Cannot find "abc.xls file".

If I first open the abc.xls file and then the summary.xls, then it
works and not viceversa.

Please update.


Debra Dalgleish replied on 31-Mar-07 03:19 PM
As answered in microsoft.public.excel.misc and

If you create the pivot table using the other Excel file as external
source, you won't have to open the source file to update the pivot table.

To change the source, right-click on a cell in the pivot table, and
choose PivotTable Wizard
Click the Back button until you reach Step 1
Choose External data source, and click Next
Click Get Data, and connect to the source Excel file and table.

Debra Dalgleish