please help!!

Posted by polo on April 08, 2001 8:36 PM

hi!
I'm trying to solve some problem, hope you can help me:

I want to sum same cell -say h321- in all sheets in workbook. I have a large workbook (around 45 sheets) and they update daily. The problem is that I can't pre-format it because some days it's shorter (say 15 sheets)and some it's larger ( maybe 65 sheets), and they are copied from vendors' workbooks. Hope I made it clear and someone can help me. Thanks.

Posted by Dave Hawley on April 08, 2001 9:46 PM

Hi Polo

For this to work effectivly you would need a User defined function such as the one I have written below:

Function SumAllSheets(SumCells As Range)
Dim WShts As Worksheet
Dim Result
Application.Volatile
For Each WShts In ActiveWorkbook.Worksheets
Set SumCells = WShts.Range(SumCells.Address)
Result = WorksheetFunction.Sum(SumCells) + Result
Next
SumAllSheets = Result
Set SumCells = Nothing
End Function

To place it in push Alt+F11 and paste in the code.

Push Alt+Q to return to Excel.

Save your Workbook.

Push Shift+F3 and select "SumAllSheets" under "User Defined" and click ok.

The put:

=SumAllSheets(H331)

You could also use the standard Sum by typing:
=SUM(
then select your first sheet and point to cell H331 on the first sheet. Then hold down the Shift key and click the last sheet. But a problem will arise if you insert/delete a sheet NOT between the first and last sheets you want to sum.

Dave

OzGrid Business Applications

Posted by polo on April 09, 2001 9:58 AM

Dave: It works great!!! Thank you VERY MUCH.

Posted by H. Root on April 09, 2001 3:34 PM

Add two blank sheets - one Start Sheet and one End Sheet. Sheets can then be inserted or deleted between these without messing up the formula.

Posted by polo on April 09, 2001 7:31 PM

Thank you H.Root also!!!!