I spent some time staring at the formula in Sheet1 and thinking it looks unnecessarily complicated. Maybe I'm mistaken about that; I'm still staring.

But I can answer at least the immediate question: If you're worried what happens if you sort the rows on the Distance sheet, consider changing the formula in Sheet1!A: Instead of putting literal names in there, put "=Distance!RC1". That is, refer to whatever is on the same row in col A of the Distance sheet. Then if you sort the rows in Distance, the names in Sheet1 will be sorted too, and still yield correct results.

If you want the names in Sheet1 to remain in the same order no matter what you do in Distance, then...hm. (Falls silent awhile, thinking.) I think it might be simpler to do it using VBA. But then I always think that.

That worked really nicely, thank you! I don't really need the Sheet1 tab to be in the same order, so long as the rank columns are accurate. I just wanted to make sure activity on one sheet wouldn't screw up another. Thanks again!