Change existing pivot table's data source to external workbook

[Gideon1973]

Hi there!

I have a workbook with dozens of pivot tables defined, and their data sources are set to named ranges in various worksheets within the same workbook.

Due to a change in customer requirements (don't you love those!), I now need to bust the detailed data out into separate workbooks, but the pivot tables need to look at the detailed data in an "external" workbook.

I first select a cell in one of my pivot tables, go to the Options Tab, and Select "Change Data Source". Then, in the "Table/Range" box, it currently has my named range. When I click the button to choose the new data source, I switch windows to reference the other, "external" workbook. When I highlight the cells I'm interested in feeding into the pivot table, and click OK, I get an error indicating "Reference is not valid".

I don't know if I can reference a named range on an external sheet in this way, and that's okay. Ultimately, I think I need to pass in the workbook's path/file name and a named or address range. However, I'm stuck on syntax.

Here are details on the existing and future setup for these objects:

• Current Named Range pivot references: =System_Data
• External workbook path: G:\Exports\System_Data_Raw.xlsx
• Worksheet containing data: [System_Data_Raw]

So, my question is is there a way I can manipulate the existing pivot tables to make them point to an external file by changing the data source property? I can maybe do it from scratch, but a lot of time and configuration went into a plethora of pivot tables, and I'd hate to have to do them all over again if I can help it.

I'm using Excel 2007 BTW...

Ideas?

Thanks!

 

[Gideon1973]

So, after some tinkering around, I got it to work -- a little bit.

I basically need to set the data source to something like this:
'G:\Exports\[System_Data_Raw.xlsx]System_Data_Raw'!$A$1:$AD$17

Now, I'd really rather would like to have a named range, but I can live with defining it in this way. The number of rows will grow over time, so I'll have to set the end part out quite far and filter out blanks in the pivot table.

However, I now have another problem -- all my counts/sums in the pivot tables are all zeros (but i do see some of the new row labels when I manually manipulate some of the row values).

Any thoughts on how to refresh the counts so they actually show up?

Thanks again!

 

[Gideon1973]

Okay, I basically had to keep grinding it out...

It appears that the format of my newly exported data is storing the numbers as text. Once I change to format to numeric, my counts show up in the pivot table. I guess next I need to figure out how to control the format of the data as it exports so the pivot table calculates as expected.

I think I'm good for now (although I am still curious if you can reference a named range from an external spreadsheet) as a pivot table source.

Thanks for reading!

 

[nakashian]

Hi Everyone,

i know this is a pretty old thread... but i'm having an issue with excel 2010, the Use an external data source is dimmed!! what can i do?
i have existing pivot tableS (around 20-30 in 1 workbook), and i need to change the data source to read from an external source (i.e. closed workbook).
any help?

 

[mendosi]

Belated response, but the steps I just posted in reply to a similar question
http://www.mrexcel.com/forum/excel-...ata-source-external-internal.html#post3961508
might answer the questions you both posed.