Hi g'
As I read it, you want the order of the numbers in column A to remain the same.
You want to change the numbers (scores) in column C.
After recording the new (total) scores in column C, you want to sort the scores using a Descending value sort on column C.
You want the team names to be rearranged to keep each team on the same row as its (sorted) score.
Here's a sample, using 9 teams and made up scores over a period of three weeks. The three images are of the same table.
Teams with names ending in an even number gained points during week 2. Those with names ending in odd numbers did not. The gains were enough to reverse the order of the score totals for the even number teams. Odd number teams kept the same scores, and sank to the lower half of the standings, but kept the same order with respect to the rest of the odd number teams.
In Week 3, only the odd number teams gained points. In some cases the gain was enough to move them up the list, but for team 9, the 10 point gain left the team still in last position.
How it works:
Column A contains a formula, entered into A2, then filled down into all cells bellow that in column A:
A2: =ROW()-1
Column B contains fixed data. The names of the teams are entered once, and do not change.
Column C contains entered data. The numbers will change from week to week as new scores are entered.
After entering the new scores, the whole table is sorted, using "Sort Descending" on the values in column C (Scores).
The numbers in column A are recalculated automatically.
One issue: Note that Team 7 and Team 3 have identical scores, but different ranks (or standings). With identical scores (and nothing else used to determine the rankings), these two teams should have the same rank, determined by the number of teams with better scores, as shown below:
Five teams have scores greater than 15, so he rank of Team 3, and the rank of Team 7 are both 6.
Seven teams have better scores than Team 2, so Team 2's rank is 8.
Another week later, there may be more than one pair of teams with the same scores
No team has a better score than Team 7, and no team has a better score than Team 3. They are both at the top of the standings, in the number 1 position.
Two teams have better scores than Team 9, putting Team 9 in the number 3 position.
ROW()-1 won't do this, as it is aware only of its own position in the table.
RANK is a better function to use here, as it uses the actual values determining each team's standing, and calculates the team's rank based on that score.
A2: =RANK(C,$C,largest is high)
Fill the formula down the rest of column A.
Regards,
Barry