Inquire split database with pivot table

What your asking for can be done....but it's a bit tricky!

We'll do this using MS Query

First, open a new workbook

<data><pivot table>....Check: External data...Click [Next]
Click [Get Data]....Database: Excel files
(Browse to your file...Click [OK])

MSQuery will display your sheet names followed by Dollar Signs.
(The $ tells MS Query to treat the sheet as a Table)

Add Paris$ to the query.....Click [OK]
Drag City, Client, Quantity to the query area
Click [ ! ] to run the query.

So far so good? (I hope)

Now comes the fun part....
Click the [SQL] button to view the SQL code.

You'll see something like this:

Please Login or Register  to view this content.

Lets modify that SQL....
1) Copy all of it an paste it into NOTEPAD
2) Edit it to this:

Please Login or Register  to view this content.

3) Copy that SQL and paste it under itself 2 times
4) Separate the 3 instance with this text:
UNION ALL

5) Now it looks like this:

Please Login or Register  to view this content.

6) Change the 2nd and 3rd sheet references to London$ and New York$:

Please Login or Register  to view this content.

7) Copy the new SQL into the SQL window (replacing the old SQL)
8) Cross your fingers and click [OK]
-You'll get a warning that the query can't be viewed in design mode
(not a problem....Click [OK])

If you see the data results....then all is well!
Click the "door" button to return the data to Excel.

Now that you're in the Pivot Table dialog again...
Click [Layout]
ROW:
drag City here
drag Client here
DATA: drag Quantity here.
COLUMN: (leave this area empty)
Click [OK]....select where you want the Pivot Table and you're mostly done.

If the Pivot Table looks ok....
1) Open the "Pivot Table.xls" file
2) Switch to the new workbook with your pivot table
3) MOVE the pivot table sheet into the "Pivot Table.xls" file
4) Done!

Post back if you have any questions.

Originally Posted by Norie

Ron

I actually had a feeling you could go down that route, or something similar
anyway, but the OP seemed to only want to use the standard options for
creating a pivot table.

You could be right, but, Barak only mentioned not wanting to use Multiple
Consolidaton Ranges (one of Pivot Table's weaker "features" IMHO) which
couldn't do the job anyway.

Obviously, there's no built-in, one-click method to consolidate the data in
Excel. However, if it simply must be done...even if somewhat inelegantly
..we're left with the Three Little Pigs' Dilema: "Straw, Sticks, or Bricks"

I used "Sticks"
(Using MS Access would be one of the Bricks approaches.)

I'm glad that worked for you.....Thanks for letting us know!