adding sheets together
 

I'm attempting to create a monthly report consisting of the sum of
values from about 20 worksheets in a workbook. Here's a rough sketch
of what I'm looking at:

Product Value1 value2 value3
1 1 2 3
2 4 5 6
3 7 8 9
4 10 11 12
5 13 14 15

So let's say there's about 20 sheets that contain these values in
them, and I need to add them all together, to create a summarized
report that looks identical. I'm going crossed-eyed trying to think
of a solution to this. I know there are more archaic ways of doing
this, but I want to make this efficient and quick, perhaps using a
table. I'm in the process of using macros to automate all of the
daily reports that are included in this workbook, and would like to do
the same for the monthly. I would appreciate any help anyone might be
able to offer.

adding sheets together
 

Do you mean this Andrea
http://www.rondebruin.nl/copy2.htm

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Andrea" wrote in message m...
I'm attempting to create a monthly report consisting of the sum of
values from about 20 worksheets in a workbook. Here's a rough sketch
of what I'm looking at:

Product Value1 value2 value3
1 1 2 3
2 4 5 6
3 7 8 9
4 10 11 12
5 13 14 15

So let's say there's about 20 sheets that contain these values in
them, and I need to add them all together, to create a summarized
report that looks identical. I'm going crossed-eyed trying to think
of a solution to this. I know there are more archaic ways of doing
this, but I want to make this efficient and quick, perhaps using a
table. I'm in the process of using macros to automate all of the
daily reports that are included in this workbook, and would like to do
the same for the monthly. I would appreciate any help anyone might be
able to offer.

adding sheets together
 

I'm a bit lost as to what the problem is exactly...

Do the number of worksheets change from month to month?

If not my initial thought would simply be to setup the formulas which
may be a little tedious because of the number of worksheets but once
setup they should be fine.

=SUM(Sheet1!A5,Sheet2!A5, etc

or if the products don't appear on the same line every month then there
are two options (niether of which are perfect but):

vlookup method:
=SUM(Vlookup(ProductName, Sheet1 Range, COLUMN(current cell),FALSE),
Vlookup(ProductName, Sheet2 Range, COLUMN(current cell),FALSE),
Vlookup(ProductName, Sheet3 Range, COLUMN(current cell),FALSE),

but you are likely to exceed the cells maximum string length with 20
sheets.

or via an array formula (which I'd recommend):

={SUM((Sheet1 ProductID column = ProductID)* Sheet1 Value1 Column)+
SUM((Sheet2 ProductID column = ProductID)* Sheet2 Value1 Column)+
SUM((Sheet3 ProductID column = ProductID)* Sheet3 Value1 Column) +
etc...

VB I don't think will make this problem that much easier although if the
number of sheets do fluctuate doing a loop would be helpful.

Mat N

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

adding sheets together
 

Thank you for all of your tips! Here is part of my final solution, in
case you're interested!


i = 1
Do Until i intTotalSheets
strSheetDate = Worksheets(i).Name
intDayOfWeek = Weekday(strSheetDate)
Set rngSalesShipped = Worksheets(i).Cells.Find _
(what:="SALES SUM SHIPPED")
Set rngSalesShipped = rngSalesShipped.Offset(1, 0)
Set rngNewRec = Worksheets(i).Cells.Find _
(what:="NEW REC")
Set rngNewRec = rngNewRec.Offset(1, 0)
p = 1
Do Until p 60
tblProductTable(p, 1) = tblProductTable(p, 1) _
+ rngSalesShipped.Value
tblProductTable(p, 2) = tblProductTable(p, 2) _
+ rngNewRec
Set rngSalesShipped = rngSalesShipped.Offset(1, 0)
Set rngNewRec = rngNewRec.Offset(1, 0)
p = p + 1
Loop
i = i + 1
Loop


Thanks Again!
Andrea

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!