how to call Sum function from cell in workbook and refresh on any change
Posted on 2014-11-07
I have multiple sheets in a workbook. On a few of the sheets, I want to sum the value in F3. I want the sum to appear on a specific sheet titled and location and be an automatic update if the values in any of the sheets change.
I have this function in the Module of the workbook:
Function SumSeries(SumRange As Range) As Double
Dim ws As Worksheet
Dim TopTotals As Double
TopTotals = 0
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Metrics" And ws.Name <> "TFSData" And ws.Name <> "TFSBugs" Then
TopTotals = TopTotals + WorksheetFunction.Sum(ws.Range(sum_Range.Address))
SumSeries = TopTotals
I have tried placing variations in the cell like =SumSeries($F3) but I get #VALUE. How do I structure what should be in the cell formula to call the function, and have it automatically update when any value within the Sum is updated?