Hi Bjorn,
MS Query can be used directly from Excel without any use of Access.
Below are some instructions on how to use MSQuery to create a data source for one or more PivotTables using xl2010 or xl2007.
The Data Ranges to be consolidated must be have the exact same headers in the same order.
These instructions assume they are on separate sheets of the same workbook.
The titles in blue font below represent the
titles of the dialog windows that you'll see as you proceed.
Define Named Ranges:
Define a separate Named Range for each Data Range to be consolidated.
This example assumes they are named MyDataA, MyDataB, MyDataC, and MyDataD.
Include the header row when selecting the range.
The Named Range must reference a fixed range (may not be a Dynamic Named Range).
Save and Close the Workbook
Open a NEW blank workbook
Launch the PivotTable Wizard:
Click on any sheet then key: Alt-D then P
PivotTable and PivotChart Wizard Step 1 of 3
Select External data source, PivotTable > Next
PivotTable and PivotChart Wizard Step 2 of 3
Click Get Data...
Choose Data Source
Excel Files* > OK > (Browse to your file with the 4 named ranges and select it) > OK
Query Wizard - Choose Columns:
Select the Name "MyDataA" then click the ">" button to select its fields
Next >
Query Wizard - Filter Data:
Click the cancel button to short cut wizard steps that are not needed
You'll see a prompt "Do you wan to continue editing this query in Microsoft Query?
Click Yes
A Microsoft Query Window will open
Click on the menubar button labeled "SQL"
Copy and Paste this text into the SQL statement: box (replacing the existing statement).
SELECT * FROM MyDataA
UNION ALL
SELECT * FROM MyDataB
UNION ALL
SELECT * FROM MyDataC
UNION ALL
SELECT * FROM MyDataD
You'll see a prompt: "SQL Query can't be represented graphically. Continue anyway?
Click OK
Click on the menubar button with the Exit Door and arrow
PivotTable and PivotChart Wizard Step 2 of 3
Click Next
PivotTable and PivotChart Wizard Step 3 of 3
New Worksheet > Finish
You should now have a PivotTable started in a new worksheet.
Set up your PivotTable report using the PivotTable Field List just as you would if you had a single range as your data source.
If you want, you can move your PivotTable into the same workbook as the source data.
When your source data changes, simply refresh your PivotTable and it will be updated.
If you add or delete rows from your source data, make sure that your named ranges are adjusted to the size of the new dataset.
That's it! There are a lot of steps, but after doing it a few times it's easy to remember the process.
