Sheet tab in formula



I wonder if we can we modify the formula 


so as to function like:


in which cells A1 and B1 contain sheet names like 1, 2, etc.

Thank you!



Selected Answer

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

    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

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


Thanks for the response. Here, what I mean is: How to adjust a (sum) formula so that it will take the sheets mentioned in the cells in consideration. For example, having the sheet names as 1, 2, 3, 4 and so on, if entered 2 in A1 and 4 in B1, the formula should be able to add the values in B5 in each of 2, 3 and 4 sheets. Similarly, if 3 in A1 and 5 in B1, the result should count on the sheets 3, 4 and 5. If indirect function doesn't go with it, could there be any other way out?
Chhabi Acharya (rep: 109) Feb 25, '19 at 9:40 am
I’m afraid there wouldn’t be a solution to your problem that avoids VBA.
Variatus (rep: 2613) Feb 25, '19 at 10:19 am
I'm grateful for your help! Thank you very much!
Chhabi Acharya (rep: 109) Feb 25, '19 at 4:31 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login