How to Filter Rows with Formulas including Cell Reference

[Snixerdoodle]

I currently have a formula that references cells on another sheet based on multiple criterias. After much googling I've gotten it to work properly however I now need to sort the data without affecting the values.
Currently if I sort as normal, the values change due to the cell references changing with the sort.

I know a simple fix is to simply copy and paste the values only and then sort them however I'd like to avoid that.

Here is my formula:<!-- BEGIN TEMPLATE: bbcode_code -->
<code>=COUNTIFS('Test Sheet'!A2:'Test Sheet'!A9818, ">="&A2,'Test Sheet'!A2:'Test Sheet'!A9818,"<="&C2,'Test Sheet'!J2:'Test Sheet'!J9818,A4)</code>


<!-- END TEMPLATE: bbcode_code -->Here is a copy of my Excel sheet minus any sensitive information:
https://www.dropbox.com/s/btrna04gc8...Test.xlsx?dl=0

Thank you in advanced!

 

[NARAYANK991]

Hi ,

I do not know which range you wish to sort , but the first thing to do is to revise your formula so that it reads :

=COUNTIFS('Test Sheet'!$A$2:'Test Sheet'!$A$9818, ">="&$A$2,'Test Sheet'!$A$2:'Test Sheet'!$A$9818,"<="&$C$2,'Test Sheet'!$J$2:'Test Sheet'!$J$9818,A4)

The $ signs are probably important.

 

[Snixerdoodle]

Hi ,

I do not know which range you wish to sort , but the first thing to do is to revise your formula so that it reads :

=COUNTIFS('Test Sheet'!$A$2:'Test Sheet'!$A$9818, ">="&$A$2,'Test Sheet'!$A$2:'Test Sheet'!$A$9818,"<="&$C$2,'Test Sheet'!$J$2:'Test Sheet'!$J$9818,A4)

The $ signs are probably important.

Perfect, this fixed my issue, thank you!