# Extending the range

0

In relation to the question in

https://www.teachexcel.com/talk/3398/sheet-tab-in-formula

The answer from variatus is perfect. Here, I tried to include calculation for other cells (in the same column) like B6, B7 and so on with the same code but it only added all values in B5 instead of giving a separate result in B6 or B7 in the first sheet.

So, what should be added to achieve this purpose or should this code be repeated for each cell?

Thanks!

### Discussion

The UDF looks like it is only supposed to return a single value into a single cell anyway; maybe I'm just not understanding exactly what you are trying to do, but note that that UDF was made very specifically to help with the question that was asked. Can you show us the code and formula that you are working with so far and maybe an example?
don (rep: 1655) Feb 27, '19 at 8:29 am
Asim (rep: 2) Feb 27, '19 at 11:37 pm

0

In the variation of my earlier UDF, to which your question refers, you can specify the cell to be summed in the function call. Call the function like =Sum3D("B5") to sum up the values in B5 in all sheets between (and including) the sheets specified in A1:B1. You can specify any single cell or even a range of cells.

``````Function Sum3D(CellAddress As String) As Variant
' 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 Double
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
If Worksheets(i).Name = ToWs Then Exit For
Next i
Sum3D = Fun
Exit Function

ErrExit:
Sum3D = -1              ' change this to "ERROR" if you prefer
End Function``````

Note that the earlier function returns an integer which I changed here to become a Variant of Double format. Therefore this function can add up money correctly, for example, or even scientific numbers. In case of error in A1:B1 you can make this function return text, like "Missing Tab" in place of the simple "-1" currently programmed above as inherited from the previous UDF.