UDF to sum corresponding cells on tabs (tabs are range argument)

[mountainman88]

I would like some code to create a UDF which works as follows:

The UDF has one argument which is a range, the range can be any size and each cell in the range must contain a valid tab name in the same workbook (tab names may have spaces). The UDF then adds together the cells on each tab in the range with the same cell reference the UDF is entered into.

For example lets call the UDF SUMTAB. The SUMTAB UDF is entered into cell B3 as below, and the range argument contains 4 tab names (tab1, tab2 etc). The output would be the sum of cell b3 on tabs1-4.

Book3
	A	B
1
2
3		=SUMTAB(A5:A8)
4
5	tab1
6	tab2
7	tab3
8	tab4
UDF

I would also like a 2nd version of the UDF called SUMTABRNG with an extra argument where the cell range that is summed can be specified:

See below the 1st argument is now a range a1:b3, this range will be what is summed across all tabs in the 2nd argument

Book3
	A	B	C
1
2
3		=SUMTABRNG("a1:b3",A5:A8)
4
5	tab1
6	tab2
7	tab3
8	tab4
UDF

Thanks in advance!

 

[gordsky]

Will your tab names always be in column a and in a single column?

 

[mountainman88]

No, there is an argument which is a range, the tab names will be in that range. Yes to single column.

thanks

 

[JoeMo]

Here's the first UDF. I have to go offline now, but I'll look at the 2nd part later if it's still open.

Book1
	A	B
1
2
3		10
4
5	tab1
6	tab2
7	tab3
8	tab4
UDF

Cell Formulas
Range	Formula
B3	B3	=SUMTAB(A5:A8)

Function SUMTAB(R As Range) As Double
Const whichCell As String = "B3"
Dim c As Range
For Each c In R
    If Not SheetExists(c.Value) Then
        SUMTAB = CVErr(xlErrValue)
        Exit Function
    Else
        SUMTAB = SUMTAB + Evaluate("'" & c.Value & "'!" & whichCell)
    End If
Next c
End Function
Function SheetExists(shName As String) As Boolean
SheetExists = False
With ThisWorkbook
    For Each sh In .Sheets
        If sh.Name = shName Then
            SheetExists = True
            Exit For
        End If
     Next sh
End With
End Function

 

[gordsky]

Here's the first UDF. I have to go offline now, but I'll look at the 2nd part later if it's still open.

Book1
	A	B
1
2
3		10
4
5	tab1
6	tab2
7	tab3
8	tab4
UDF

Cell Formulas
Range	Formula
B3	B3	=SUMTAB(A5:A8)

Function SUMTAB(R As Range) As Double
Const whichCell As String = "B3"
Dim c As Range
For Each c In R
    If Not SheetExists(c.Value) Then
        SUMTAB = CVErr(xlErrValue)
        Exit Function
    Else
        SUMTAB = SUMTAB + Evaluate("'" & c.Value & "'!" & whichCell)
    End If
Next c
End Function
Function SheetExists(shName As String) As Boolean
SheetExists = False
With ThisWorkbook
    For Each sh In .Sheets
        If sh.Name = shName Then
            SheetExists = True
            Exit For
        End If
     Next sh
End With
End Function

If Im reading the code correct it will always calculate on "B3". The op was asking for a code that calculates on the cell the udf was being entered to (so i would take that to mean if entered in c5 then the calcs would be on c5 across the range of sheets etc). May need to change the cell reference to "application.thiscell.address"

 

[JoeMo]

OK, I got a reprieve on running an errand. Here's the second UDF. You really don't need the first UDF as this one easily covers a single cell or a contiguous range of cells on each tab.

Book1
	A	B
1
2
3		24
4
5	tab1
6	tab2
7	tab3
8	tab4
UDF

Cell Formulas
Range	Formula
B3	B3	=SUMTABRNG("A1:B3",A5:A8)

Function SUMTABRNG(whatRange As String, R As Range) As Double
Dim c As Range
For Each c In R
    If Not SheetExists(c.Value) Then
        SUMTABRNG = CVErr(xlErrValue)
        Exit Function
    Else
        SUMTABRNG = SUMTABRNG + Evaluate("Sum(" & "'" & c.Value & "'!" & whatRange)
    End If
Next c
End Function
Function SheetExists(shName As String) As Boolean
SheetExists = False
With ThisWorkbook
    For Each sh In .Sheets
        If sh.Name = shName Then
            SheetExists = True
            Exit For
        End If
     Next sh
End With
End Function

 

[JoeMo]

If Im reading the code correct it will always calculate on "B3". The op was asking for a code that calculates on the cell the udf was being entered to (so i would take that to mean if entered in c5 then the calcs would be on c5 across the range of sheets etc). May need to change the cell reference to "application.thiscell.address"

Actually, I think the second UDF is best to use for both purposes. see my subsequent post #6.

 

[gordsky]

Actually, I think the second UDF is best to use for both purposes. see my subsequent post #6.

your first definately needed the

Const whichCell As String = "B3"

changing as tied the calcs specifically to B3 and not the cell the udf was typed in.
Totally agree with you re post#6. Would accomplish both requests by OP.

 

[mountainman88]

This is amazing thanks, the reason I wanted both is because I assume the 2nd one is more processor intensive than the first? Also if I want to just drag across and down the first one is easier because I don't have put in a dynamic reference to the cell the formula is in? WHat is the best way to put in a dynamic reference to that cell in the 2nd function?

thanks

 

[mountainman88]

your first definately needed the

Const whichCell As String = "B3"

changing as tied the calcs specifically to B3 and not the cell the udf was typed in.
Totally agree with you re post#6. Would accomplish both requests by OP.

What would this line need to be changed to, so it refernces the cell the formula is in?

thanks