Sorting Issue Using a Nested Formula

[avendi]

Hey everyone,

I have attached a spreadsheet with two tabs, named Over 3m Data and Replacement List.

On the Over 3m Data tab I want to establish if sites are on the Replacement List tab and return the answer as Yes or No. To do so I used the formula

=IF(COUNTIF('Replacement List'!$A$1:$A$74,'Over 3m Data'!A2),"Yes","No")

This works well. However, if anyone sorts the data on the Over 3m Data tab differently, eg. by site # lowest to highest, the cell reference remains fixed. Is there any way around this?

Hope it makes sense.

Cheers, AV