kurtinsc said:
Add another sheet labeled "prior week".
When loading a new file, put the one in the 2011 sheet in the "prior week" sheet prior to loading the new one in.
Replicate the formulas referencing the 2011 sheet in a new columnt referencing the "prior week" sheet.
Reference that total from each team when populating the "previous ranking" column on the overall ranking sheet.
You end up with something like this:
Team 2010 Points 2011 Points Total point rank 2011 Points Gain/loss Weekly change Weekly Change Rank Prior Week Rank
microdose 7499 2292 1 2066 -5207 226 74 1
Kevin Rudd's Hairdryer 7413 2158 2 1867 -5255 291 61 2
Pcutter 7495 2033 3 1647 -5462 386 40 3
auto de fé 7500 1822 4 1365 -5678 457 22 4
Bicing 7498 1753 6 1358 -5745 395 37 5
trotters aotearoa 7457 1710 7 1349 -5747 361 45 6
Spider1964 7423 1577 13 1345 -5846 232 72 7
Moondance 7478 1535 17 1334 -5943 201 83 8
LukeSchmid 7488 1602 10 1273 -5886 329 48 9
Tuarts 7466 1467 20 1260 -5999 207 78 10
lancaster 7497 1478 19 1248 -6019 230 73 11
Jakob747 7477 1802 5 1204 -5675 598 3 12
coppiman 7498 1582 12 1159 -5916 423 28 13
AAC1983 7495 1656 8 1153 -5839 503 15 14
Sneekes 7385 1601 11 1146 -5784 455 23 15
Waterloo Sunrise 7471 1628 9 1126 -5843 502 16 16
Joy118118 7500 1546 16 1125 -5954 421 29 17
Handbrake 7496 1528 18 1118 -5968 410 32 18
sublimit 7457 1431 23 1113 -6026 318 53 19
jaylew 7499 1405 25 1092 -6094 313 55 20
Jiaying 7475 1412 24 1091 -6063 321 52 21
Mellow Velo 7499 1387 26 1059 -6112 328 49 22
Object 7410 1566 15 1023 -5844 543 9 23
MADRAZO 7500 1439 22 993 -6061 446 24 24
Moncoutie 7499 1384 27 988 -6115 396 36 25
The Amateur 7492 1262 36 973 -6230 289 62 26
theyoungest 7498 1576 14 949 -5922 627 1 27
kurtinsc 7495 1446 21 903 -6049 543 9 28
Ham-N-Eggs 7500 1250 38 888 -6250 362 44 29
henry olonga 7500 1364 28 887 -6136 477 18 30
mc_mountain 7500 1272 35 887 -6228 385 41 30
Kwibus 7500 1346 30 869 -6154 477 18 32
AntonioRossi 7493 1180 43 863 -6313 317 54 33
Jan the Man 7499 1027 58 825 -6472 202 81 34
Ferminal 7473 986 61 814 -6487 172 85 35
Barrus 7436 1146 48 810 -6290 336 47 36
EvansIsTheBest 7500 1319 31 809 -6181 510 13 37
nobilis 7499 1117 50 805 -6382 312 57 38
who dat 7231 1017 59 802 -6214 215 76 39
skidmark 7490 1201 41 802 -6289 399 35 39
Sydney21 7500 921 67 802 -6579 119 87 39
Armchair cyclist 7500 1222 40 794 -6278 428 27 42
al_pacino 7496 1097 52 784 -6399 313 55 43
Bumeington 7492 1348 29 765 -6144 583 5 44
Parrulo 7473 1295 32 750 -6178 545 7 45
Kvinto 7479 1148 47 742 -6331 406 34 46
Timmy-loves-Rabo 7488 1179 44 742 -6309 437 25 46
Contre la montre 7500 1173 45 736 -6327 437 25 48
ingsve 7476 1226 39 724 -6250 502 16 49
The Cobra 7471 1131 49 713 -6340 418 30 50
Gotland 7494 1259 37 699 -6235 560 6 51
togo95 7450 1285 33 689 -6165 596 4 52
Jancouver 7500 1198 42 687 -6302 511 12 53
nvpacchi 7470 1094 53 686 -6376 408 33 54
Zaschrona 7488 967 63 680 -6521 287 64 55
Sasquatch 7350 1064 54 669 -6286 395 37 56
Jpettersen 7408 758 76 663 -6650 95 88 57
deValtos 7491 918 68 662 -6573 256 68 58
The Hitch 7459 1281 34 661 -6178 620 2 59
mortand 7500 1046 56 657 -6454 389 39 60
pabryan 7411 1003 60 656 -6408 347 46 61
Hugo Koblet 7495 966 64 654 -6529 312 57 62
auscyclefan94 7485 932 65 646 -6553 286 65 63
Jukebox 7493 1156 46 612 -6337 544 8 64
Squire 7499 983 62 606 -6516 377 42 65
Spare Tyre 7492 1110 51 605 -6382 505 14 66
Passista 7496 889 69 590 -6607 299 59 67
royalpig180 7492 1056 55 584 -6436 472 20 68
XlandaluzeX 6805 782 73 575 -6023 207 78 69
tgsgirl 7495 922 66 550 -6573 372 43 70
zapata 7494 797 72 544 -6697 253 70 71
sosososik 7468 818 70 530 -6650 288 63 72
whittashau 7327 1045 57 525 -6282 520 11 73
Kazistuta 7497 801 71 506 -6696 295 60 74
Rocksteady 7469 734 77 478 -6735 256 68 75
Team Sky Fans 7480 666 80 461 -6814 205 80 76
Arnout 7490 763 75 440 -6727 323 51 77
roundabout 7490 719 78 434 -6771 285 66 78
Libertine Seguros 7492 635 82 418 -6857 217 75 79
Blues in the bottle 7498 658 81 405 -6840 253 70 80
jens_attacks 7500 707 79 383 -6793 324 50 81
Erick 8-A 7495 588 84 375 -6907 213 77 82
will10 7469 536 86 359 -6933 177 84 83
sigsky 7499 525 87 323 -6974 202 81 84
Vonn Brinkman 7474 775 74 305 -6699 470 21 85
hrotha 7500 561 85 282 -6939 279 67 86
luckyboy 7500 631 83 216 -6869 415 31 87
Wiggins_fan 3292 167 88 43 -3125 124 86 88