# 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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Domenic

##### MrExcel MVP
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

##### Well-known Member
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

##### New Member
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
4
Views
452
Replies
5
Views
538
Replies
1
Views
532
Replies
1
Views
412
Replies
3
Views
762

1,171,790
Messages
5,877,506
Members
433,264
Latest member
Donnybrook

### 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.

### Which adblocker are you using?

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