Microsoft Office Forums Macro Excel
 Register FAQ Search Today's Posts Mark Forums Read

#1
07-13-2011, 02:46 AM
 Il_Fabietto Windows 2K Office 2003 Novice Join Date: Jun 2009 Posts: 7
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

A 7
B 6
C 9
A 5
B 2
G 2
A 2

Gen Feb Mar....Dic
A 14 ...
B 8 ...
C 9 ...

G 2 ...

Tot 33 ... ...

I hope I was clear

#2
07-14-2011, 01:43 PM
 pkrishna Windows Vista Office 2007 Novice Join Date: Jul 2011 Posts: 16

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
07-14-2011, 01:51 PM
 pkrishna Windows Vista Office 2007 Novice Join Date: Jul 2011 Posts: 16

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
07-28-2011, 02:40 AM
 Catalin.B Windows Vista Office 2007 Expert Join Date: May 2011 Location: Iaşi, Romānia Posts: 386

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...
Attached Files
 collector.xlsx (10.9 KB, 3 views) source.xlsx (13.8 KB, 4 views)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post soma104 Word 1 04-14-2011 05:10 PM Jazz43 PowerPoint 0 03-05-2011 09:06 AM HuaMin Excel 2 03-22-2010 08:11 PM milplus Project 1 03-02-2010 01:25 PM pawan2paw Excel 1 06-04-2009 12:28 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:53 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top