Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Average cells from multiple sheets (excel2000)

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

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •