Sheet tab in formula

The INDIRECT function doesn't work with 3D referencing. Depending on what you wish to have in A1:B1 there might be a work-around.

One common way is to name blank sheets, like, "Start" and "End" and then use a formula like =SUM(Start:End!B5) which would sum all sheets located between the two blank sheets, irrespective of their names.

As an alternative you might install the UDF code below in a standard code module and call it from a worksheet as =SumB5().

Function SumB5() As Integer
    ' this function will return -1 if the sheet in Sheet1!A1 doen't exist.
    ' It will return the sum of all sheet from Sheet1!A1 to end if Sheet1!B1 doesn't exist

    ' observe the addressing of "Sheet1" by its CodeName
    ' rather than its name, like in Worksheets("Sheet1").
    ' This has the effect that you can change the name of this
    ' sheet without needing to change the code.

    Dim Fun As Integer
    Dim FromWs As String
    Dim ToWs As String
    Dim i As Integer

    Application.Volatile
    FromWs = Sheet1.Cells(1, "A").Value
    ToWs = Sheet1.Cells(1, "B").Value

    On Error GoTo ErrExit
    For i = Worksheets(FromWs).Index To Worksheets.Count
        Fun = Fun + Worksheets(i).Cells(5, "B").Value
        If Worksheets(i).Name = ToWs Then Exit For
    Next i
    SumB5 = Fun
    Exit Function

ErrExit:
    SumB5 = -1              ' return -1 if an error occurred
End Function