View Single Post
 
Old 02-22-2020, 02:40 PM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote