reference cell changing in formula

efish

New Member
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.

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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!

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?

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

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

Replies
0
Views
413
Replies
5
Views
174
Replies
1
Views
312
Replies
1
Views
272
Replies
7
Views
358

1,211,458
Messages
6,101,969
Members
447,766
Latest member
cool_aikon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back