reference cell changing in formula

efish

New Member
Joined
Jan 8, 2004
Messages
35
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,385
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 28, 2004
Messages
76,351
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 8, 2004
Messages
35
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"))
 

Forum statistics

Threads
1,171,805
Messages
5,877,621
Members
433,272
Latest member
Aakash Majethia

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top