Sum Cell range based on Worksheet title with wildcard.

[ImThatGuy]

I have a problem that I cannot solve.

I have a pricing rollup tab that needs to reference all sheets with the Name "Price." and sum values in cell G105.
The issue lies with there can be any text after "price."
It also varies as it could be a number or it could be a state or a year.
It also varies in the number of times the word Price appears in each file. The worksheet name "Price.xxxx" could appear twice or it could be 50 times.

For Example
Price.2018
Price.AR
Price.AR.2018
Price.1015567678

I started to write the a macro that outputs all sheet names in the range BQ1:BQ150 and I was going to use that sheet list to identify the word Price and use the wildcard character
Code:

Sub ListSheets() 
Dim ws As Worksheet
Dim cellNum As Integer
 
cellNum = 1
'Output list of sheets  
For Each ws In Worksheets
     Sheets("Price.Rollup").Cells(cellNum, "BQ") = ws.Name
     cellNum = cellNum + 1
Next ws
 
End Sub

The sheets I need to reference always start With "Price." and then the wildcard text
G105 is the cell I need to sum across all sheets and input on G105 on the rollup tab .
Cell B1 Contains the word "Price." on the Rollup Tab to reference and the use a wildcard indicator to perform the rest of the sheet name.

The current formula I have is as follows:
Code:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"BQ1:BQ105"),B1&"."&"*",INDIRECT ("'"&sheets&"'!"&"G105")))
If gives me the result #NAME ? as you cannot have wildcard with SumProduct.


It would be much easier if i could just write this as a macro to stick in the template workbook before it gets sent out to each area.

Also, I cannot post a copy of the Workbook as it contains confidential pricing information. So my apologies in advance for that.

Please let me know if anymore information is needed.

Thanks All!

[Paul_Hossler]

Not tested, but maybe something like this

Option Explicit

Const addrPrefix As String = "B1"
Const addrDataToSum As String = "G105"

Sub Rollup()
    Dim ws As Worksheet
    Dim dSum As Double
    Dim sPrefix As String
    
    'trim prefix and make UC and add * for the Like
    sPrefix = Trim(UCase(Worksheets("Rollup").Range(addrPrefix))) & "*"     '   <<<<<< change
    
    For Each ws In ActiveWorkbook.Worksheets
        If UCase(ws.Name) Like sPrefix Then
            dSum = dSum + ws.Range(addrDataToSum)
        End If
    Next
    
    Worksheets("Rollup").Range(addrPrefix).Value = dSum
End Sub

[ImThatGuy]

Not tested, but maybe something like this

Option Explicit

Const addrPrefix As String = "B1"
Const addrDataToSum As String = "G105"

Sub Rollup()
    Dim ws As Worksheet
    Dim dSum As Double
    Dim sPrefix As String
    
    'trim prefix and make UC and add * for the Like
    sPrefix = Trim(UCase(Worksheets("Rollup").Range(addrPrefix))) & "*"     '   <<<<<< change
    
    For Each ws In ActiveWorkbook.Worksheets
        If UCase(ws.Name) Like sPrefix Then
            dSum = dSum + ws.Range(addrDataToSum)
        End If
    Next
    
    Worksheets("Rollup").Range(addrPrefix).Value = dSum
End Sub

Thanks Paul, This worked great.
I made one change to the code (last line) to where the output value was:

Worksheets("Rollup").Range(addrPrefix).Value = dSum

'Changed to

Worksheets("Rollup").Range(addrDataToSum).Value = dSum

[Paul_Hossler]

I made one change to the code (last line) to where the output value was:

Ooops -- glad you caught it. Careless Copy/Paste on my part