# How to do recursive calculations in Excel?

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?

-
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

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.
-