Thread: Average cells from multiple sheets (excel2000)

1. Average cells from multiple sheets (excel2000)

I have a workbook where the user can generate and name as many worksheets as they want.
I want to roll up the values from all the sheets to a results sheet that shows various calculations from values in all the sheets.
Eg. Average the value form A1 in all the sheets.
How do I loop through all the sheets that might be created to average the cell values.
Scott

2. Re: Average cells from multiple sheets (excel2000)

Use a 3d reference:
=Average(FirstSheet:LastSheet!A1)

Steve

3. Re: Average cells from multiple sheets (excel2000)

Steve,
Thanks
The problem with your solution is that I won't know what the sheet names are going to be.
As I understand 3D references need the beginning and ending sheet names.
Scott

4. Re: Average cells from multiple sheets (excel2000)

Insert a blank sheet FirstSheet to the left of all existing sheets and a blank sheet LastSheet to the right of all existing sheets.
Instruct users that they should only add new sheets in between.
Since aggregate functions such as SUM, MAX and AVERAGE ignore blanks, a formula such as =AVERAGE(FirstSheet:LastSheet!A1) will return the correct answer.

5. Re: Average cells from multiple sheets (excel2000)

In addition to Hans' suggestion you could create a UDF which loops thru all the sheets:

<pre>Option Explicit
Function AvgAll(sCell As String)
Dim wks As Worksheet
Dim dSum As Double
Dim iCount As Integer
Application.Volatile
iCount = 0
dSum = 0
For Each wks In Worksheets
iCount = iCount + Application.Count(wks.Range(sCell))
dSum = dSum + Application.Sum(wks.Range(sCell))
Next
If iCount = 0 Then
AvgAll = CVErr(xlErrDiv0)
Else
AvgAll = dSum / iCount
End If
Set wks = Nothing
End Function</pre>

Use it like:
=AvgAll("A1")

or even:
=AvgAll("A1:A10")

Steve

6. Re: Average cells from multiple sheets (excel2000)

Thanks all,
I like Steves solution since the workbook will be a template type of application.
This way I won't have to have the user see any blank sheets before or after the sheets they add.
Scott

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•