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]

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

thanks

i think you would change the "B3" to application.thiscell.address (you may not need the .address but not in a position to test at present)

 

[JoeMo]

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

You are welcome - thanks for the reply. Here's a dynamic reference to the cell the UDF is in for the first UDF:

Function SUMTAB(R As Range) As Double
Dim c As Range, whichCell As String
whichCell = Application.ThisCell.Address(0, 0)
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

You can take the same approach for the 2nd UDF, but what exactly do you want to do with the address of the cell the UDF is in for the case where you have already entered the range to be summed in each tab?

 

[mountainman88]

You are welcome - thanks for the reply. Here's a dynamic reference to the cell the UDF is in for the first UDF:

Function SUMTAB(R As Range) As Double
Dim c As Range, whichCell As String
whichCell = Application.ThisCell.Address(0, 0)
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

You can take the same approach for the 2nd UDF, but what exactly do you want to do with the address of the cell the UDF is in for the case where you have already entered the range to be summed in each tab?

I meant a relative reference as the first argument when using the UDF. Because the argument is text, when you drag across and down it repeats the same text. Sorry for the confusion!

 

[JoeMo]

I meant a relative reference as the first argument when using the UDF. Because the argument is text, when you drag across and down it repeats the same text. Sorry for the confusion!

Maybe my imagination is blunt tonight, but the best I can come up with is something like below. This UDF will sum a range for each tab in the tab list whose upper left-most cell on the tab is the cell that the UDF is entered in (on the UDF tab) and is Rws rows by Cols columns in size. This can be dragged down or across to change the Rws x Cols upper left-most cell. Note that absolute references must be used on the Tab List range (3rd argument for the UDF). As an example, if this UDF is entered in B2 of the UDF tab:

=SUMTABRNG1(4,3,$A$5:$A$8)

The sum range for each tab in the tab list ($A$5:$A$8) would be B2:D5 (12 cells on each tab). Dragging down and/or across changes the upper left-most cell of the 12-cell sum range.

Function SUMTABRNG1(Rws As Long, Cols As Long, R As Range) As Double
Dim c As Range, whichCell As String, whatRange As String
whichCell = Application.ThisCell.Address(0, 0)
whatRange = Range(whichCell).Resize(Rws, Cols).Address(0, 0)
For Each c In R
    If Not SheetExists(c.Value) Then
        SUMTABRNG1 = CVErr(xlErrValue)
        Exit Function
    Else
        SUMTABRNG1 = SUMTABRNG1 + 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

 

[mountainman88]

Joe could you possibly help me out on this one? Probably even simpler than the above request.

Generate a formula across tabs based on a table

I have a workbook with 3 tabs named Summary1 - Summary3, and 7 tabs named tab1-tab7 I have a table on another tab in the workbook as below ABC1Summary1Summary2Summary32Tab1Tab3Tab73Tab2Tab44Tab5 I need VBA that loops through each column and performs the following: 1.Select summary tab named...

www.mrexcel.com

Thanks!