#1
|
|||
|
|||
Macro Excel
Hello, I imagine that this problem has already been discussed.
I have 12 spreadsheets (one per month) with a few columns, I would like to create on the last sheet (13 °) a macro that generates a table with a column in the months in a row and the sum of column values grouped in individual sheets. Let me explain spreadsheets 1....12 A 7 B 6 C 9 A 5 B 2 G 2 A 2 Output spreadsheet 13 Gen Feb Mar....Dic A 14 ... B 8 ... C 9 ... G 2 ... Tot 33 ... ... I hope I was clear Thanks for your cooperation |
#2
|
|||
|
|||
It is not very clear to me, but take a look at the following:
Col:ACol:BCol:CCol:ACol:BCol:CCol:ACol:BCol:CCol:ACol:BCol:CCol:ACol:BRow:2JanFebMarAprMayJunRow:3A 7B0G5B3G6B3Row:4B6G4A6A5A0A6Row:5C9C0B7G0B4A7Row:6A5B6C7C7A9G8Row:7B2A2G4A3G5A9Row:8G2B3A4B6A0A2Row:9A 2G7B1A1B2B3Row:10Row:11JanFebMarAprMayJunRow:12A142109924Row:13B898966Row:14C907700Row:15G21190118Bear with the crummy notation I had to employ to designate the Rows and Columns.In your case each of the monthly numbers and categories inside a box will be on a separate worksheet.Note that the category order in each worksheet is different.The summarized information is presented in the second table.The formula here is: =SUMIF(C$3:C$9,$B12,D$3$9) - for "A-Jan"Explanation: SumIF function takes three arguments. A range foe the criteria, the Criteria, and the Range to be summedHere the number 14 for A-Jan is arrived at by considering the range c$3-c$9, comparing with the value in $B12 ("A") and if there is a match, summing the corresponding entry in the range D$3-D$9The '$' before the row number or column designation freeze the value from being incremented as you move across or down.So, the number 11 for G-May is obtained by =SUMIF(K$3:K$9,$B15,L$3:L$9)This is trivial if you have some experience with Excel, but would look intimidating for a noviceLet me know if it is clear enough. |
#3
|
|||
|
|||
Sorry about the previous reply. What looked like a passable formatting in the preview got horribly changed in the posted version.
Let me try again. Hope this image version stays intact. No, the picture is no more than a generic icon. Sorry for cluttering up the space. |
#4
|
|||
|
|||
why do you need a macro for that...
Looks like a simple task, you can create a collector workbook, connected to the monthly workbook, as the samples atached. When you have a new workbook with data, just use a copy of the collector workbook with changing the data source (Data-edit links-change source-and browse to where your new data is located-select file-ok) if your task is more complicated, you have to give more details... |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Importing data from excel using a macro | soma104 | Word | 1 | 04-14-2011 05:10 PM |
Macro: chart from excel into PP as objects | Jazz43 | PowerPoint | 0 | 03-05-2011 09:06 AM |
Excel macro (vba) | HuaMin | Excel | 2 | 03-22-2010 08:11 PM |
import tasks from excel with a macro o add-in | milplus | Project | 1 | 03-02-2010 01:25 PM |
Help! for using an appropriate function/Macro in Excel | pawan2paw | Excel | 1 | 06-04-2009 12:28 PM |