You might be able to use MS Query to consolidate Excel ranges from your
multiple wkshts.:
This example uses 3 named ranges in 3 different sheets of the same workbook
that contain data from Row_1 through Row_65536.
(Each range contain 2 columns: Name, Amount)
Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Name, Amount)
--->Columns are in the same order.
The data in each wkbk is in named ranges.
--->I used:
rngS1Data for Sheet1 data,
rngS2Data for Sheet2 data,
rngS3Data for Sheet3 data,
(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)
Save and CLOSE the data workbook.
Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start
2)<Data><Import External Data><New Database Query>
....Databases: Excel Files
Browse to the data file, pick ONE of the the data ranges.
--->Accept defaults until the next step.
Create the data view you want to see,
INCLUDING criteria that will limit the number
of returned rows to only a few
Return the data to Excel and contstruct the pivot table.
Next, you need to edit sql code of the query so it includes the other data
sheets.
You have 2 options here:
1) edit the sql in notepad
or
2) download the Pivot Play PLUS add-in from
http://www.contextures.com/xlPivotPlayPLUS01.html
(I would..and did...use Pivot Play PLUS because it was designed
specifically to assist with editing the SQL code that drives
pivot and query tables)
For this example, though....
Right click on the Pivot Table
....select: Pivot Table Wizard
....Click the [Back] button
....click: the [Get Data] button
(That will open MS Query)
Click the [SQL] button
Replace the displayed SQL code with an adapted version of this:
SELECT * FROM
(
SELECT tblData.Name, tblData.Amount
FROM `C:\ExcelQueries\FullMultiSheetData`.rngS1Data tblData
UNION ALL
SELECT tblData.Name, tblData.Amount
FROM `C:\ExcelQueries\FullMultiSheetData`.rngS2Data tblData
UNION ALL
SELECT tblData.Name, tblData.Amount
FROM `C:\ExcelQueries\FullMultiSheetData`.rngS3Data tblData
)
WHERE tbldata.Amount BETWEEN 10 AND 20
That query appends all of the data into one recordset
and filters it to only show Amounts between 10 and 20
Note: Apostrophes in the SQL code ( ` )are located on the tilde key (~)
Return the data to Excel.
Once that is done....to get the latest data:
Right Click in the data range
....<Refresh Data>
You can edit the query SQL at any time to
add/remove data sources and/or fields.
Does that help?
Post back if you have more questions.
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
