How do I automatically sort a table in Excel every time one of its values is updated?

Question

I'm keeping the results from a season of the baseball board game Strat-O-Matic in an Excel spreadsheet (Excel 2011 for Mac). The sheet has the league schedule and league standings. I've already set it up so that whenever I enter the score of a game, the standings are updated to reflect the new win-loss records of the teams who participated in that game.

After I enter a game's score, I would like for the standings to be sorted automatically instead of having to manually sort by winning percentage. If it makes a difference, there are actually four different standings tables, one for each of four divisions in this league. They should all be individually sorted by winning percentage every time the spreadsheet is updated.

Any help is appreciated, thank you.

Solution

To sort the table you will need to write a VBA script. Generally speaking, formula in a cell cannot have an effect on the other cell's content (the other cell's formula to be precise; one cell can change the value of the other cells if the other cells contains a formula that refer to it).

As a workaround, as you are going to sort the destination table (i.e. standings) after updating the source table (i.e. schedule), we can do some sorting indirectly:

1. Modify the source table so that the ranking is automatically calculated
2. Update the destination table so that its contents are looked up from the source table by VLOOKUP.

Step 1

Make the ranking the first column (i.e. column A in this example) and make the formula like this:

  A                                               |  B   |   C
--------------------------------------------------+------+-------------------
Rank                                              | Team | Winning Percentage
=COUNTIF(C$2:C$9,">"&C2)+1                        | A    | 0.05
=COUNTIF(C$2:C$9,">"&C3)+COUNTIF(C$2:C2, C3)+1    | B    | 0.99
=COUNTIF(C$2:C$9,">"&C4)+COUNTIF(C$2:C3, C4)+1    | C    | 0.81
=COUNTIF(C$2:C$9,">"&C5)+COUNTIF(C$2:C4, C5)+1    | D    | 0.92
=COUNTIF(C$2:C$9,">"&C6)+COUNTIF(C$2:C5, C6)+1    | E    | 0.54
=COUNTIF(C$2:C$9,">"&C7)+COUNTIF(C$2:C6, C7)+1    | F    | 0.15
=COUNTIF(C$2:C$9,">"&C8)+COUNTIF(C$2:C7, C8)+1    | G    | 0.15
=COUNTIF(C$2:C$9,">"&C9)+COUNTIF(C$2:C8, C9)+1    | H    | 0.40

Please update the range by yourself. The first COUNTIF counts how many teams has a winning percentage higher than it, and the second COUNTIF counts how many teams ties with it. This is important because we need do not want duplicated ranking to confuse the VLOOKUP.

For instance the above example looks like:

Rank | Team | Winning Percentage
8    | A    | 5%
1    | B    | 99%
3    | C    | 81%
2    | D    | 92%
4    | E    | 53%
6    | F    | 15%
7    | G    | 15%
5    | H    | 40%

As you can see team F and team G have the same winning percentage, they are assigned different ranking.

Step 2

In your destination table (i.e. standings) you need to update it using lots of VLOOKUP:

  A  |   B                                         |   C
-----+---------------------------------------------+------------------------------------------
Rank | Team                                        | Winning percentage
1    | =VLOOKUP($A2,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A2,Schedule!$A$1:$C$9,3,FALSE)
2    | =VLOOKUP($A3,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A3,Schedule!$A$1:$C$9,3,FALSE)
3    | =VLOOKUP($A4,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A4,Schedule!$A$1:$C$9,3,FALSE)
4    | =VLOOKUP($A5,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A5,Schedule!$A$1:$C$9,3,FALSE)
5    | =VLOOKUP($A6,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A6,Schedule!$A$1:$C$9,3,FALSE)
6    | =VLOOKUP($A7,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A7,Schedule!$A$1:$C$9,3,FALSE)
7    | =VLOOKUP($A8,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A8,Schedule!$A$1:$C$9,3,FALSE)
8    | =VLOOKUP($A9,Schedule!$A$1:$C$9,2,FALSE)    | =VLOOKUP($A9,Schedule!$A$1:$C$9,3,FALSE)

And you will get your results like:

Rank | Team | Winning percentage
1    | B    | 99%
2    | D    | 92%
3    | C    | 81%
4    | E    | 53%
5    | H    | 40%
6    | F    | 15%
7    | G    | 15%
8    | A    | 5%