Home |
Search |
Today's Posts |
#1
|
|||
|
|||
grand total column B from every worksheet in workbook
I want to have a function in a cell that totals column B from every
worksheet in my workbook even w/o knowing how many worksheets there are or what the name of any worksheet is. Even better, because I would like to have a "summary" worksheet, the function should skip column B in its own work sheet -- so, the function I'd like might total every number in a column called "Qty" for any worksheet in the workbook that has a column named "Qty". I have an Excel doc that holds all of my sales data. The data is imported as a text file from a webstore db into a worksheet named for the current month - e.g., 05Jan. Every worksheet is based on a template, with each column labeled. I'd like a summary sheet that would keep a running total of sales quantity (every column labeled "Qty") -- i.e., how many units have been sold since Day 1. Suggestions would be appreciated. TIA. -- Igor |
#2
|
|||
|
|||
To sum column B of every worksheet except the current
one, you could use: Function SumColumnB() As Long Dim ws As Worksheet Dim TotalQty As Long Dim SheetQty As Long Dim ActiveWSQty As Long Application.Volatile ActiveWSQty = Application.Sum(ActiveSheet.[B:B]) For Each ws In ThisWorkbook.Worksheets SheetQty = Application.Sum(ws.[B:B]) TotalQty = TotalQty + SheetQty Next SumColumnB = TotalQty - ActiveWSQty End Function -- To use, press ALT+F11, go to Insert Module, and paste in the code above. Press ALT+Q to close the window. Call the function in the worksheet as: =SumColumnB() HTH Jason Atlanta, GA -----Original Message----- I want to have a function in a cell that totals column B from every worksheet in my workbook even w/o knowing how many worksheets there are or what the name of any worksheet is. Even better, because I would like to have a "summary" worksheet, the function should skip column B in its own work sheet -- so, the function I'd like might total every number in a column called "Qty" for any worksheet in the workbook that has a column named "Qty". I have an Excel doc that holds all of my sales data. The data is imported as a text file from a webstore db into a worksheet named for the current month - e.g., 05Jan. Every worksheet is based on a template, with each column labeled. I'd like a summary sheet that would keep a running total of sales quantity (every column labeled "Qty") -- i.e., how many units have been sold since Day 1. Suggestions would be appreciated. TIA. -- Igor . |
#3
|
|||
|
|||
On Wed, 23 Feb 2005 08:43:46 -0800, "Jason Morin"
wrote: To sum column B of every worksheet except the current one, you could use: Function SumColumnB() As Long Dim ws As Worksheet Dim TotalQty As Long Dim SheetQty As Long Dim ActiveWSQty As Long Application.Volatile ActiveWSQty = Application.Sum(ActiveSheet.[B:B]) For Each ws In ThisWorkbook.Worksheets SheetQty = Application.Sum(ws.[B:B]) TotalQty = TotalQty + SheetQty Next SumColumnB = TotalQty - ActiveWSQty End Function Wow, that's perfect. I did have to learn that I have to manually update the cell to be sure that it is current as I switch between worksheets. But after that, works fine. Thanks, Jason. -- Igor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy a column in worksheet with a character change | Excel Worksheet Functions | |||
Update second worksheet with changes in original worksheet | Excel Worksheet Functions | |||
How do I ROUNDUP a Column of cells on a worksheet template? | Excel Worksheet Functions | |||
how to copy 2350 hyperlink full paths to any column in a worksheet ? | Excel Discussion (Misc queries) | |||
Seach Column and return multiple dates to another worksheet? | Excel Worksheet Functions |