If it’s a list that can’t be sorted, like an itinerary, the usual trick is to add an adjacent column of sequential numbers and sort on that. But that’s work, isn’t it?
No problem. It’s easy enough using OFFSET:
The formula is:
=OFFSET($A$1,COUNTA(CTWynberg)-ROW(),0)
How it works
Here “CTWynberg” is a named range for the stations in Column A. OFFSET’s first parameter after the reference is a row. COUNTA returns 12, ROW() returns 1. Hang on … 12 less 1 is 11! Why doesn’t it return Kenilworth from row 11? The reason is that OFFSET is zero based, so what is being returned is the 11th row indeed, but of the list named CTWynberg, not the worksheet, and that list is numbered 0 to 11.
“And”, Telly Savalas would say, “like that”.
Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS can assist you with your Excel: enquiries@krs.co.za