Sorting Issue Using a Nested Formula

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

Hey GC Excel,

Great, many thanks for fixing that for me, and for the explanation. Just on that explanation, does Excel therefore assume the A2 refers to the sheet on which the formula was written?

Cheers, AV

Thanks you very much