Cell Reference Issue When Sorting Column Containing Formula

[Carrie Kaelin]

I am using the following contif formula: =COUNTIF(Sheet2!H:H,Sheet1!A2). The formula calculates the correct answer, but when I sort the data, the cell reference does not change for the criteria. I can copy and paste special for values only before sorting, but I would like to eliminate this extra step. Is there a better formula that may be used to avoid this problem?

Thanks!

 

[VoG]

In the Name Manager name Sheet1!A2 as MyVal

Then try

=COUNTIF(Sheet2!H:H,MyVal)

 

[Carrie Kaelin]

Many thanks for your help!