How can you sum the same cell across multiple worksheets by worksheet index number in excel?

I am trying to sum cell Y116 across all valid worksheets in my excel workbook. To help define the valid worksheets, I wrote a VBA function, SHEETNAME(number), that returns the name of the worksheet at the given worksheet index (number). I did this because the names and number of valid worksheets will never be constant, however the valid range will always start at the 3rd worksheet (i.e. SHEETNAME(3)) and will always end at the third from last worksheet (i.e. SHEETNAME(SHEETS()-2)) in my workbook.

I feel like this should be relatively straightforward with both SUM() and INDIRECT(), but I keep getting reference errors (#REF!).

I can get the string formatted how I want it with

="'"&SHEETNAME(3)&":"&SHEETNAME(SHEETS()-2)&"'!Y116"

but I get a reference error when I try to put it all together:

=SUM(INDIRECT("'"&SHEETNAME(3)&":"&SHEETNAME(SHEETS()-2)&"'!Y116"))

I know cell Y116 is a valid reference in all of my worksheets because I can hardcode the formula with the actual names of the worksheets instead of the index and I get the answer I am looking for. Any advice?

Here is the SHEETNAME() function: Function SHEETNAME(number As Long) As String SHEETNAME = Sheets(number).Name End Function

Solution

To do what you want with SUM(INDIRECT()), on needs to return an array of sheet names to the indirect.

To do this one will need to change the UDF to:

Function SHEETNAME(srt As Long, ed As Long) As Variant
Application.Volatile

If ed - srt <= 0 Then Exit Function

Dim temp() As Variant
ReDim temp(1 To ed - srt + 1) As Variant

Dim i As Long
For i = srt To ed
    temp(i - srt + 1) = Worksheets(i).Name
Next

SHEETNAME = temp
End Function

Then one can use:

=SUM(INDIRECT("'"&SHEETNAME(3,SHEETS()-2)&"'!Y116"))

But if you are going to iterate the sheets anyway, why not just do the sum in the UDF:

Function MY3DSUM(srt As Long, ed As Long, add As String) As Double
Application.Volatile

If ed - srt <= 0 Then Exit Function

Dim temp As Double
temp = 0

Dim i As Long
For i = srt To ed
     temp = temp + Worksheets(i).Range(add).Value2
Next

MY3DSUM = temp
End Function

Then you would call it:

 =MY3DSUM(3,SHEETS()-2,"Y116")