Home |
Search |
Today's Posts |
#1
|
|||
|
|||
adding worksheets with conditions
Hi,
I have a workbook with 50 sheets I am trying to add and summarize the product sales by country and week number. Each sheet has the same lay out. The two variables are country and week number Each sheet is country specific, however they are not grouped together, ie one after the other as the data is summarized on a week to week basis. I need a formula to go to each sheet check if it meets the conditions and add this to the next sheet and so on to give me the total. I have a global summary for all weeks ie total sales, but this is done in each sheet by summary by country in each worksheet and adding the result together. Example Week # 9 Country USA Product A B C D E Has anyone any ideas? I have tried SUMIF, SUMPRODUCT, VLOOKUP |
#3
|
|||
|
|||
Thanks Bernie,
THe worksheets are too big to combine onto one sheet. I will have to try something else. Thanks Niall "Bernie Deitrick" wrote: Niall, You need to change your workbook structu multiple sheets are often a bad design. Combine the table from all the sheets together onto one sheet, adding a column that identifies the whatever it was that is unique to the old sheets. When you have everything on one sheet, you can either use SUBTOTAL formulas combined with filters to give you your results, or use a Pivot Table (the prefered method). HTH, Bernie MS Excel MVP "Niall D" <Niall wrote in message ... Hi, I have a workbook with 50 sheets I am trying to add and summarize the product sales by country and week number. Each sheet has the same lay out. The two variables are country and week number Each sheet is country specific, however they are not grouped together, ie one after the other as the data is summarized on a week to week basis. I need a formula to go to each sheet check if it meets the conditions and add this to the next sheet and so on to give me the total. I have a global summary for all weeks ie total sales, but this is done in each sheet by summary by country in each worksheet and adding the result together. Example Week # 9 Country USA Product A B C D E Has anyone any ideas? I have tried SUMIF, SUMPRODUCT, VLOOKUP |
#4
|
|||
|
|||
Niall,
You could store the data in a database, and still use Excel's Pivot Table functionality. HTH, Bernie MS Excel MVP "Niall D" wrote in message ... Thanks Bernie, THe worksheets are too big to combine onto one sheet. I will have to try something else. Thanks Niall "Bernie Deitrick" wrote: Niall, You need to change your workbook structu multiple sheets are often a bad design. Combine the table from all the sheets together onto one sheet, adding a column that identifies the whatever it was that is unique to the old sheets. When you have everything on one sheet, you can either use SUBTOTAL formulas combined with filters to give you your results, or use a Pivot Table (the prefered method). HTH, Bernie MS Excel MVP "Niall D" <Niall wrote in message ... Hi, I have a workbook with 50 sheets I am trying to add and summarize the product sales by country and week number. Each sheet has the same lay out. The two variables are country and week number Each sheet is country specific, however they are not grouped together, ie one after the other as the data is summarized on a week to week basis. I need a formula to go to each sheet check if it meets the conditions and add this to the next sheet and so on to give me the total. I have a global summary for all weeks ie total sales, but this is done in each sheet by summary by country in each worksheet and adding the result together. Example Week # 9 Country USA Product A B C D E Has anyone any ideas? I have tried SUMIF, SUMPRODUCT, VLOOKUP |
#5
|
|||
|
|||
Bernie,
Got it sorted. Thanks "Bernie Deitrick" wrote: Niall, You could store the data in a database, and still use Excel's Pivot Table functionality. HTH, Bernie MS Excel MVP "Niall D" wrote in message ... Thanks Bernie, THe worksheets are too big to combine onto one sheet. I will have to try something else. Thanks Niall "Bernie Deitrick" wrote: Niall, You need to change your workbook structu multiple sheets are often a bad design. Combine the table from all the sheets together onto one sheet, adding a column that identifies the whatever it was that is unique to the old sheets. When you have everything on one sheet, you can either use SUBTOTAL formulas combined with filters to give you your results, or use a Pivot Table (the prefered method). HTH, Bernie MS Excel MVP "Niall D" <Niall wrote in message ... Hi, I have a workbook with 50 sheets I am trying to add and summarize the product sales by country and week number. Each sheet has the same lay out. The two variables are country and week number Each sheet is country specific, however they are not grouped together, ie one after the other as the data is summarized on a week to week basis. I need a formula to go to each sheet check if it meets the conditions and add this to the next sheet and so on to give me the total. I have a global summary for all weeks ie total sales, but this is done in each sheet by summary by country in each worksheet and adding the result together. Example Week # 9 Country USA Product A B C D E Has anyone any ideas? I have tried SUMIF, SUMPRODUCT, VLOOKUP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding values across worksheets in a spreadsheet | Excel Worksheet Functions | |||
adding rows to several worksheets | Excel Discussion (Misc queries) | |||
Adding rows of different info from separate worksheets into summar | New Users to Excel | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
Adding more than three Conditions to 'Conditional Formatting' | Excel Discussion (Misc queries) |