Pivot Tables from more than one tabs

[Guest]

I have data that is longer than 63K (Excel row limit) and end up having more
than one tab of data. Also sometime the data is kept in different Excel files
by month.

Question: is it possible to create pivot tables using more than one tab or
from more than one Excel file ?

 

[Guest]

I suspect that, for that much data, MS Access would be a better repository....
but, since you're asking about Excel....

You can probably base the Pivot Table on MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts. This also works for consolidating
data from the active workbook (Just save it first so Excel can find it):

This example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

The data in each wkbk must be in named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)<Data><pivot table>
Source: External Data....Click the [Get DAta] button
Databases: Excel Files

Browse to one of The files, pick The data range to import.
--->Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Dept1111`.rng1111Data
UNION ALL
SELECT * FROM `C:\Dept2222`.rng2222Data
UNION ALL
SELECT * FROM `C:\Dept3333`.rng3333Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng4444Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng5555Data

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just right-click in the data
range and select: Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thank you Ron for your great response.

I will try out the MS query option tomorrow. I had not even thought of that.

MS Acess is not an option, i was using that before IT in their wisdom
removed Access from all PC's.

I suspect that, for that much data, MS Access would be a better repository....
but, since you're asking about Excel....

You can probably base the Pivot Table on MS Query to consolidate Excel
ranges from your multiple wkbks/wkshts. This also works for consolidating
data from the active workbook (Just save it first so Excel can find it):

This example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

The data in each wkbk must be in named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)<Data><pivot table>
Source: External Data....Click the [Get DAta] button
Databases: Excel Files

Browse to one of The files, pick The data range to import.
--->Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Dept1111`.rng1111Data
UNION ALL
SELECT * FROM `C:\Dept2222`.rng2222Data
UNION ALL
SELECT * FROM `C:\Dept3333`.rng3333Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng4444Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng5555Data

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just right-click in the data
range and select: Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

I have data that is longer than 63K (Excel row limit) and end up having more
than one tab of data. Also sometime the data is kept in different Excel files
by month.

Question: is it possible to create pivot tables using more than one tab or
from more than one Excel file ?