Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding sheets | Excel Discussion (Misc queries) | |||
adding up sheets that arent there yet | Excel Worksheet Functions | |||
spread sheets - adding | Excel Worksheet Functions | |||
Adding Sheets | Excel Discussion (Misc queries) | |||
Adding relevant sheets | Excel Worksheet Functions |