Change existing pivot table's data source to external workbook

Gideon1973

Board Regular
Joined
Apr 23, 2012
Messages
126
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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!
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top