Hi all,
Looking for some help with an esports F1 league table so it will sort automatically when I input the positions based on the total points column. Currently when I enter the positions, the total points column uses a sumproduct from another table on the page to carry across the corresponding points. P1 is 35 points, P2, 30 points etc. currently I have to do a custom sort manually.
I want to also sort the table automatically. I've attached a mini-sheet of what things are in what cells.
if anyone can assist you would be superstars!
Thank you.
Looking for some help with an esports F1 league table so it will sort automatically when I input the positions based on the total points column. Currently when I enter the positions, the total points column uses a sumproduct from another table on the page to carry across the corresponding points. P1 is 35 points, P2, 30 points etc. currently I have to do a custom sort manually.
I want to also sort the table automatically. I've attached a mini-sheet of what things are in what cells.
if anyone can assist you would be superstars!
Thank you.
TSD TABLE NEW 2021.xlsm | |||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | |||
1 | 1 | 35 | |||||||||||||||||||||||||||||||||||||
2 | 2 | 30 | |||||||||||||||||||||||||||||||||||||
3 | GAMERTAG | penalty | XTRA PTS FOR | 3 | 27 | ||||||||||||||||||||||||||||||||||
4 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | FASTEST LAP | TOTAL PTS | PTS DIFF | 4 | 24 | ||||||||||||
5 | 1 | 1 | ERN Massey | N/a | N/a | 2 | 2 | 4 | 1 | 1 | 7 | 5 | 7 | 2 | 1 | 3 | 3 | 4 | 8 | 371 | 0 | 5 | 22 | ||||||||||||||||
6 | 2 | 2 | Grit86 (allan) | 4 | 5 | 9 | 7 | DNF | 5 | 3 | 8 | 7 | 2 | 5 | 3 | 9 | 6 | 7 | 3 | 1 | 320 | -51 | 6 | 20 | |||||||||||||||
7 | 3 | 3 | raratron89 | 2 | 3 | 11 | 6 | 7 | 4 | 10 | 3 | 11 | 4 | 4 | 10 | 6 | 5 | 9 | 5 | 316 | -4 | 7 | 18 | ||||||||||||||||
8 | 4 | 4 | Xi Rashy10 | 5 | 4 | 4 | 8 | 9 | 6 | 4 | DNF | 4 | 11 | 3 | DNF | DNF | 4 | 3 | DNS | 3 | 259 | -57 | 8 | 16 | |||||||||||||||
9 | 5 | 5 | RRF1 R3DM4N | 6 | 6 | 8 | 13 | 10 | 11 | DNF | 4 | 13 | 3 | DNF | 5 | 11 | 11 | 10 | 7 | 217 | -42 | 9 | 14 | ||||||||||||||||
10 | 6 | 6 | SF1 Goomba | 8 | 7 | 5 | 10 | DNF | 10 | 6 | 6 | DNF | 10 | 9 | DNS | 10 | 7 | 6 | 9 | 210 | -7 | 10 | 12 | ||||||||||||||||
11 | 7 | 7 | SyKo Swish | DNS | 1 | 6 | 4 | 3 | DNS | DNS | DNS | 3 | DNS | DNF | DNS | DNS | DNS | 1 | DNF | 2 | 170 | -40 | 11 | 10 | |||||||||||||||
12 | 8 | 8 | PRF1 CLEMM | N/a | N/a | N/a | N/a | 6 | DNS | DNS | DNF | 9 | 5 | 6 | 8 | 12 | 8 | 5 | 4 | 1 | 164 | -6 | 12 | 9 | |||||||||||||||
13 | 9 | 9 | XRF1 Rodge T | N/a | N/a | N/a | N/a | N/a | N/a | N/a | Trial | 8 | 6 | 7 | 6 | 8 | 9 | 11 | 6 | 134 | -30 | 13 | 8 | ||||||||||||||||
14 | 10 | 10 | ERN BigG | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | 11 | 4 | 2 | 10 | 8 | 2 | 1 | 123 | -11 | 14 | 7 | |||||||||||||||
15 | 11 | 11 | C11 MRDALBY | N/a | N/a | N/a | N/a | 12 | 9 | 5 | 5 | 10 | DNS | DNS | 2 | DNS | DNF | 12 | DNS | 118 | -5 | 15 | 6 | ||||||||||||||||
16 | 12 | 12 | Gamerman882793 | 9 | DNF | DSQ | DNF | 13 | 12 | 8 | 10 | 15 | 9 | 12 | 11 | 14 | DNS | 14 | DNS | 112 | -6 | 16 | 5 | ||||||||||||||||
17 | 13 | 13 | NutmegTuchas | N/a | N/a | 14 | 12 | DNF | 13 | 7 | 9 | 12 | 8 | 10 | DNF | 13 | DNF | 13 | DSQ | 109 | -3 | 17 | 4 | ||||||||||||||||
18 | 13 | 13 | THE GUNNERS05 | 10 | 9 | 17 | DNF | 14 | DNF | 9 | 11 | 16 | 13 | 13 | DNS | DNF | 12 | 15 | 10 | 109 | 0 | 18 | 3 | ||||||||||||||||
19 | 14 | 15 | Re1 iCz | 1 | DNS | DNS | DNS | 2 | DNF | DNS | DNS | 14 | DNS | DNS | DNS | DNS | 1 | DNS | DNS | 107 | -2 | 19 | 2 | ||||||||||||||||
20 | 15 | 16 | TIR DEAN | N/a | N/a | N/a | N/a | DNF | DNS | DNS | DNS | DNS | DNS | 1 | DNF | 1 | DNS | DNS | DNS | 2 | 72 | -35 | 20 | 1 | |||||||||||||||
21 | 16 | 17 | Bekker313 | DNF | 8 | 15 | 11 | 11 | DNS | DNF | DNS | DNS | 12 | DNF | 9 | DNF | DNS | DNF | DNF | 65 | -7 | DNF | 0 | ||||||||||||||||
22 | 17 | 18 | LuckyThirteen91 | DNS | DNS | 7 | DNS | DNS | DNS | DNF | DNS | DNS | DSQ | DNS | DNS | 5 | DNF | DNS | DNS | 40 | -25 | DNS | 0 | ||||||||||||||||
23 | 18 | 19 | PRF1ViPeR | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | 1 | 35 | -5 | N/a | 0 | ||||||||||||||||
24 | 19 | 20 | Gn0PENTA | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | reserve | DNS | 0 | -35 | reserve | 0 | ||||||||||||||||
25 | 19 | 20 | VRF1 Getsuga | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | reserve | DNF | DNS | DNS | DNS | DNS | DNS | DNF | 0 | 0 | ||||||||||||||||||
26 | 19 | 20 | VRF1 Senna | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | reserve | reserve | 0 | 0 | ||||||||||||||||||
27 | 19 | 20 | Woodzy1991 | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | N/a | 0 | 0 | ||||||||||||||||||
28 | |||||||||||||||||||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AG5:AG27 | AG5 | =SUMPRODUCT(SUMIF(AK:AK,J5:AE5,AL:AL))+AF5 |
AH5 | AH5 | =SUM(AG5-AG5) |
AH6:AH27 | AH6 | =SUM(AG6-AG5) |
C5:C27 | C5 | =RANK(AG5,AG$5:AG$27) |