Pivot table: Changing location of Excel data source?

  • Thread starter Thread starter Toby Erkson
  • Start date Start date
T

Toby Erkson

I have some pivot tables that currently pull their data from another tab
within the workbook. I want to separate the data worksheet from the report
worksheets, thus the data worksheet would exist by itself in a separate
workbook. I need to have the current pivot tables point to the new
workbook!worksheet for their data. Here's the catch: The pivots tables
currently use a dynamic Name for their data range. Help!

Toby Erkson
 
Toby

Not got workbooks to test with but try this. Name a range in you new
workbook, save and close.

Go to your currently pivot table and from the Pivot table toolbar, invoke
the wizard. Press back twice and change the source from Excel to external
source (second option down), select Excel as the driver and click ok.
Select your new excel workbook and the wizard should now offer the range you
set up.

Do all the necessary to get the data back in and it should be set. As I
say, now way to test so let me know how it goes.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Ahh, I hadn't thought of changing the source to an *external* one...I'll try
that this afternoon! Thanks for the suggestion, I hope it works :-)
 
Thanks soooo much, Nick, for helping me out. After some quick
investigation -- because I simply couldn't wait to figure it out :-) -- here's
what I've discovered so far:

1. When grabbing external Excel data, DYNAMIC names do not show up but STATIC
ones do :-\

2. Grabbing data from another pivot table only returns the data currently
displayed in the table :-(

3. A usable data table in a spreadsheet will be displayed as simply the sheet
name followed by a dollar sign i.e. Data_tab$. If going this route just make
sure the column headers begin in the FIRST ROW (A1, B1, C1, etc.). There
should be nothing else on the sheet except the data. If there's spacing
between the rows/columns (for example, inserted blank rows/columns) then that
will be included in the data pull. Thus, having multiple data sources on one
sheet is a no-no unless they are named ranges and you specifically pull the
named ranges (see item #1), otherwise you're getting it all.

Looks like #3 will work for me...maybe #1 if I want to use VBA to create a
named range on the fly ;-)
 
Make sure "System Tables" is checked in MS Query for viewable objects so
you'll see the sheetname$. I think this is in an Options tab.
 
Back
Top