The INDIRECT function worked - Thank you
Here is the code if you are interested.
I'm somewhat new to programming macro's so keep that in mind.
' Sort the data by Status Code and Confidence %age - 'PT Cooling' sheet contains the raw data that changes row # based on new downloads from the database.
Sheets("PT Cooling").Select
Cells.Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
' Clear the PT Cooling Summary sheet for new input data
Sheets("PT Cooling Summary").Select
Cells.Select
Selection.Clear
' Filter the raw data for anything >99% confidence level and status code of H:Hold; I:Investigate or C: Complete
Sheets("PT Cooling").Select
Cells.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
"Search Criteria").Range("A187:B190"), Unique:=False
' Copy the results to the summary sheet
Columns("A:Q").Select
Selection.Copy
Sheets("PT Cooling Summary").Select
Range("A1").Select
ActiveSheet.Paste
' Copy the Sum Result for >99% - Value Only - to the last row of the data on the summary sheet
Sheets("Search Criteria").Select
Range("A194:P194").Copy
Sheets("PT Cooling Summary").Select
LastRow = Range("'PT Cooling Summary'!A65536").End(xlUp).Row
LastRow1 = LastRow + 1
Range("A" & LastRow1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PT Cooling").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Cells.Select
' Filter the raw data for anything between 24 and 99%
Range("A1:Q1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Search Criteria").Range("E187:G189"), Unique:=False
Columns("A:Q").Copy
' Copy and Paste onto summary sheet underneath 99% and greater data with three blank rows in between
LastRow = Range("'PT Cooling Summary'!A65536").End(xlUp).Row
LastRow3 = LastRow + 3
Sheets("PT Cooling Summary").Range("A" & LastRow3).PasteSpecial
Sheets("Search Criteria").Select
' Copy and Paste Sum result - Value Only - of data between 24 and 99% at last row of current data
Range("A195:P195").Copy
Sheets("PT Cooling Summary").Select
LastRow = Range("'PT Cooling Summary'!A65536").End(xlUp).Row
LastRow1 = LastRow + 1
Range("A" & LastRow1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("PT Cooling").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
There is more after this where I perform some more iterations based on different sums and sorts but you can get the idea from this.
The SUM formulas that I'm copying from in A194:P194 and A195:P195 now have the indirect function (see below) and the 1000 does not change. Thanks again!
=SUMIF(INDIRECT("'PT Cooling Summary'!F2:F1000"),">98",INDIRECT("'PT Cooling Summary'!J2:J1000"))