reference cell changing in formula

[efish]

I'm using a formula to sum a column that has a header row. This sum changes based on what iteration the macro is performing. I then copy and paste the result of the sum (values only) to the sheet for a summary of the sums at the different iterations.
The formula looks like this:
=SUM(IF('PT Cooling Summary'!$F$2:$F$89>98,'PT Cooling Summary'!$J$2:$J$89,0))
and I have several of these based on different criteria.

My problem is that the $F2:$F89 was $F2:$F1000 to protect for the fact that the data # of rows is variable on the PT Cooling Summary sheet, but when I ran the macro the formula changed from $F$1000 to $F$89 I'm assuming due to the fact that there was only that much data until the blank rows appeared. Does anyone know why this happened and how to keep the $F$1000 from changing? Thanks.

 

[Domenic]

Try...

=SUM(IF(INDIRECT("'PT Cooling Summary'!F2:F89")>98,INDIRECT("'PT Cooling Summary'!J2:J89")))

Better yet, as Norie has pointed out, use SUMIF instead...

=SUMIF(INDIRECT("'PT Cooling Summary'!F2:F89"),">98",INDIRECT("'PT Cooling Summary'!J2:J89"))

Alternatively, you may want to consider defining a dynamic range. This way the range automatically changes according to your data.

Hope this helps!

 

[Norie]

Can we see the actual code?

How are you determining the 89 (or whatever) ?

By the way should you not be using a SUMIF formula?

 

[efish]

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"))