4

I have a large spreadsheet document and I want to have a master worksheet that calculates fields from all of the worksheets. I know that I can just select a cell from a worksheet by explicitly naming the worksheet and then the row I'm interested in. However, is there a way to recursively select a cell so that, as I add more worksheets, they are automatically included in the calculated field in the master worksheet?

Indrek
  • 24,874
Walter Cecil Worsley IV
  • 141

1 Answers1

2

One way would be to use a VBA function that you can call in a formula. The function below will return the contents of a cell address (this will not work for ranges in its current form) across all worksheets other than the active sheet.

Public Function AcrossSheets(rngAddress As String, Optional includeThisSheet As Boolean = False, Optional wText As String) As Variant
Application.Volatile
Dim tmpResults As String, ws As Worksheet
If includeThisSheet Then
    For Each ws In Worksheets
        If wText <> "" Then
            If InStr(ws.Name, wText) <> 0 Then
                tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
            End If
        Else
            tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
        End If
    Next ws
Else
    For Each ws In Worksheets
        If ws.Name <> ActiveSheet.Name Then
            If wText <> "" Then
                If InStr(ws.Name, wText) <> 0 Then
                    tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
                End If
            Else
                tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
            End If
        End If
    Next ws
End If
tmpResults = Left(tmpResults, Len(tmpResults) - 2)
AcrossSheets = Split(tmpResults, ", ")
End Function

To use this code, press Alt+F11 in Excel, insert a new module, and paste in the code.

So, if, for example, you want to sum the values of B2 from each sheet other than the active sheet, you would use the following array formula (entered by pressing Ctrl+Shift+Enter):

=SUM(VALUE(AcrossSheets("B2")))

To include the value of B2 on the active sheet, use the array formula:

=SUM(VALUE(AcrossSheets("B2",TRUE)))

To only sum the values of B2 on sheets that include "Monthly" in the sheet name (not including the active sheet), use the array formula:

=SUM(VALUE(AcrossSheets("B2",FALSE,"Monthly")))

Note two things:

  1. AcrossSheets returns an array of values. Because of this, it should only be used in array formulas.
  2. The array returned by AcrossSheets contains values as strings (text). If you expect numerical data to be returned by the function (as in the examples above), you must wrap the function call in VALUE(). This will convert the string data to numerical data.
Excellll
  • 12,847