View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Roger Govier[_3_] Roger Govier[_3_] is offline
external usenet poster
 
Posts: 2,480
Default Consolidate Pivot table from multiple files

Hi

Multiple consolidation in Pivot Tables will not provide what you want.
You nee to add an extra field (column) to each of the sheets title Country
Fill this column with the name of the country concerned.
Copy the data from all three sheets to ne new sheet, with just a single
header row.
Use this sheet as the source data for your Pivot Table

--

Regards
Roger Govier

"aw" wrote in message
...

Would like to know the way I can consolidate 3 similar files (but in
different worksheets €“ eg. File A for China, File B for UK, ..) into one
pivot table.

I try using pivot table function and I can select only €śRow€ť, €śColumn€ť &
€śvalue€ť in €śConstruct your pivot table report€ť menu.

What I have to do is to have pivot table that can analysis all individual
fields.

Eg. Consolidated Pivot table row select : €ścountry€ť €“ from different
source
files
Consolidated Pivot table column select : €śNature€ť
And sum of €śSales€ť, €śPur€ť, €śBank€ť €¦ under the pivot table


How can I get this??


One of the source file like :
LOCATION : CHINA INV PO PRODUCT Nature SALE PUR BANK AR
SI0001 PO231 Misc Sales 300 300
SI0001 PO231 Misc Purchases 200 -200
SI0001 PO231 Misc Expenses -100
SI0001 PO231 Misc bank chg -20
SI0001 PO231 Misc payment
SI0001 PO231 Misc Amount Rec'd


SI0001 PO238 blue a Sales 300 300
SI0001 PO238 blue a Purchases 200 -200
SI0001 PO238 blue a Expenses -100
SI0001 PO238 blue a bank chg -20
SI0001 PO238 blue a payment
SI0001 PO238 blue a Amount Rec'd

SI0001 PO238 yellow c Sales 300 300
SI0001 PO238 yellow c Purchases 200 -200
SI0001 PO238 yellow c Expenses -100
SI0001 PO238 yellow c bank chg -20
SI0001 PO238 yellow c payment
SI0001 PO238 yellow c Amount Rec'd
--
aw