Sum across sheets with variable sheet names

[frankday]

My Spreadsheet has a tab for all 12 months. =SUM('Jan:Dec'!K25) works great. I want to copy that formula only have it use the sheet name where Dec is. Example: I want the sum of Jan:Jul on the July sheet and Jan:Aug on the August Sheet. But I don't want to have to change the formula on every sheet. So for that second value I want the formula to fill in the sheet name that that formula is on.

Thanks for your help.

 

[Artik]

You're going to get a little longer formula.

=SUM(INDIRECT("Jan:" & MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, LEN(CELL("filename", A1)) - FIND("]", CELL("filename", A1))) & "!K25"))

Artik

 

[frankday]

=SUM(INDIRECT("Jan:" & MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, LEN(CELL("filename", A1)) - FIND("]", CELL("filename", A1))) & "!K25"))

It doesn't seem to be working. I get #REF!. When I run the evaluation, The last evaluation I get before #REF! is SUM(INDIRECT("PP1:PP8!K25"))
The fire I'm working with has sheets PP1 - PP26

Any Thoughts?

 

[Artik]

Sorry, the INDIRECT function is not able to return a range from multiple sheets.
Perhaps the task can be solved only by using formulas, but I'm afraid it may be some kind of monster. If you allow the use of macros, below is a function that should solve the task.
In the standard module (e.g. Module1) insert the function:

Function SumAcrossSheets(startSheet As String, endSheet As String, cellAddress As String) As Double
    Dim wks As Worksheet
    Dim sum As Double
    Dim inRange As Boolean
    Dim Tmp As Variant

    'Application.Volatile

    inRange = False
    sum = 0
    endSheet = Split(endSheet, "]")(1)

    For Each wks In ThisWorkbook.Worksheets
        If UCase(wks.Name) = UCase(startSheet) Then inRange = True
        If inRange Then sum = sum + wks.Range(cellAddress).Value
        If wks.Name = endSheet Then Exit For
    Next wks

    SumAcrossSheets = sum

End Function

Call the function in the sheets. If in all sheets the result of the function is in the same cell (e.g. C2), then select sheets Jan to Dec and into C2 insert the formula:

=SumAcrossSheets("Jan",CELL("filename", A1),"K25")

Artik