Pivot table: Changing location of Excel data source?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Toby Erkson - 30 Sep 2004 21:56 GMT

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

Reply to this Message

Nick Hodge - 01 Oct 2004 07:33 GMT

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.

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

>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
[quoted text clipped - 5 lines]
>
> Toby Erkson

Reply to this Message

Toby Erkson - 01 Oct 2004 18:25 GMT

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 :-)

Signature

Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

> Toby
>
[quoted text clipped - 9 lines]
> 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.

Reply to this Message

Toby Erkson - 01 Oct 2004 19:37 GMT

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 ;-)

Signature

Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

> Toby
>
[quoted text clipped - 6 lines]
> Select your new excel workbook and the wizard should now offer the range you
> set up.

Reply to this Message

Toby Erkson - 04 Oct 2004 21:45 GMT

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.

> ...
> 3. A usable data table in a spreadsheet will be displayed as simply the sheet
[quoted text clipped - 5 lines]
> 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...

Reply to this Message