Sorting with links

When you sort a column with formulae in, if they have relative cell references then those references are automatically adjusted, in the same way as copy and paste adjusts cell references. Practical upshot is that when you sort, it looks like nothing has happened in all those cells... You could change them to absolute refs, but then you need to do each one individually - and change them each time teams swap over. Nightmare.

Have you come across the RANK() function? You can use this on your original sheet to get your desired 'Rank' column, just in the wrong order. However, you can then use the MATCH function to find out how far down the rank 1 team is, then the INDEX or VLOOKUP function to return each corresponding value for Points1, Points2, etc. That'll give you formulae that can be copied down the table, and the good thing is that it automatically changes the order when one team overtakes another. However, you need to be absolutely clear how to break ties, or it won't work - and actually, tie breaks are often where the logic gets complicated. I did something similar for the 2010 FIFA World Cup to automatically generate the Preliminary Pool tables, and the tiebreaks rules go as far along the following list as necessary: if same points, then goal difference, then goals for, then who won when they played each other, then FIFA draw lots - try coding that one...

Thanks, but that sounds nightmarish to me, too. Looking for something simple, otherwise I have to stick to manual copying values.