Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 6 worksheets into 1 worksheet - Summary
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine 6 worksheets into 1 worksheet - Summary
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)*(I NDIRECT("'"&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 --- "Ms. D" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Worksheets to a Summary Worksheet | Excel Worksheet Functions | |||
Feed Data from Multiple worksheets to a Summary Worksheet | Excel Worksheet Functions | |||
How do I combine multiple worksheets into one worksheet? | Excel Worksheet Functions | |||
Spawning worksheets and a summary per worksheet | Excel Discussion (Misc queries) | |||
Summary worksheet referencing multiple worksheets | Excel Worksheet Functions |