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 (https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=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 :(