Combine 6 worksheets into 1 worksheet - Summary

[Ms. D]

I have 6 worksheets from Jan, Feb, Mar, Apr, May, Jun that have different
information on each worksheets. And would like to have another worksheet that
would combine all the different codes (over @120 lines) with the appropriate
dollar amount for each cityand 0 dollar amount if there's no amount for that
month for that code. For Example -

Jan

Code Atlanta Chicago Detroit
300 - Bill Expense 359.23 234.23 1132.32
301 - Process Fee 65.00 45.00 50.00
400 - Bill Expense 4560.00 333.22 232.32

Feb

Code Atlanta Chicago Detroit
300 - Bill Expense 490.20 939.30 1239.33
301 - Process Fee 75.00 55.00 60.00
302 - Sales 3333 4444 5555
400 - Bill Expense 2341.50 267.92 403.72

and so on

Summary
Jan Feb Jan
Feb Jan Feb
Code Atlanta Atlanta Chicago Chicago
Detroit Detroit
300 - Bill Expense 359.23 490.20 234.23 939.30
1132.32 1239.33
301 - Process Fee 65.00 75.00 45.00 55.00
50.00 60.00
302 - Sales 0 3333 0
4444 0 5555
400 - Bill Expense 4560.00 2341.50 333.22 267.92
232.32 403.72

 

[Max]

I posted this response in your earlier thread:
--------------
Taking the set-up from your other post in .worksheet.functions
here's my thoughts ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3k364
Summarizing 6 sheets.xls

In Total,

Put in C3
=SUMPRODUCT((INDIRECT("'"&C$1&"'!A2:A200")=$A3)*(INDIRECT("'"&C$1&"'!B2:B200")=$B3)*OFFSET(INDIRECT("'"&C$1&"'!B2:B200"),,MATCH(C$2,INDIRECT("'"&C$1&"'!1:1"),0)-2,))
Copy C3 across to H3, fill down as far as required. Adapt the ranges to suit.

Notes: As detailed in the sample, a complete listing of codes & descriptions
is assumed in A3:B3 down. Sheetnames are listed in C1:H1, cities in C2:H2
like this:

Jan Feb Jan Feb Jan Feb
Atlanta Atlanta Chicago Chicago Detroit Detroit

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik