Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Are you okay with using VBA? –  Excellll Jan 27 '13 at 20:43
    
Can you specify a little bit further what you need? What do you mean with "calculates fields from all of the worksheets"? Do you want to sum cells across all worksheets? What calculation do you mean? –  Peter Albert Jan 27 '13 at 21:37
    
You probably mean loop and not recursion. Quite different approaches. –  Lee Taylor Jan 27 '13 at 22:22

1 Answer 1

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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.