Sheet tab in formula

0

Hello!

I wonder if we can we modify the formula 

=SUM('1:2'!B5)

so as to function like:

=SUM('A1:B1'!B5)

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

Thank you!

Answer
Discuss

Answers

2
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

    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
Discuss

Discussion

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