# 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!

2

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
``````

### 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: 2504) 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