Auto Update a table

divster27

New Member
Joined
Jan 19, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
I've got what i think should be a simple request... probably a little bit of VB

I've been tasked with producing a spreadsheet that shows all the remaining fixtures in the football season - serious stuff this - and i'd like the 'League Table' to update as users enter their predictions.

So when someone enters a prediction against the fixture (next to the H or A) - the total points on the far right hand side will update. that's easy enough

I'd like the Team Position in the table to move in line with their total points.

So from the paste below, if i was to put a 3 next to the Watford A fixture, the total will update to 48 in the league table, and Arsenal would move up to position 4
I know sorting is a manual task, but can it easily be automated?

Arsenal45Wolves46Spurs39United46Chelsea50West Ham42
positionTeamPoints
WatfordAWatfordHBrightonAWatfordHArsenalHWolvesH3Chelsea50
LeicesterHArsenalAArsenalHMan CityALeicesterHLiverpoolA4United46
LiverpoolHWest HamALeedsASpursHBurnleyAAston VillaH5Wolves46
Aston VillaACrystal PalaceHEvertonHLiverpoolANewcastleHSpursA6Arsenal45
Crystal PalaceAEvertonAMan UtdALeicesterHNorwichAEvertonH7West Ham42
BrightonHLeedsHWest HamHEvertonABrentfordHBrentfordA8Spurs39
SouthamptonAAston VillaHNewcastleHNorwichHSouthamptonABurnleyH
Manchester UnitedHNewcastleAAston VillaAArsenalALeedsAChelseaA
West Ham UnitedAMan CityHBrightonHBrentfordHWest HamHArsenalH
LeedsHBurnleyABrentfordABrightonAEvertonANorwichA
Newcastle UnitedABrightonHLeicesterHChelseaHWolvesHMan CityH
EvertonHChelseaALiverpoolACrystal PalaceAMan UtdABrightonA
Tottenham HotspurANorwichHBurnleyHWatfordH
ChelseaALiverpoolANorwichA
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I'd personally have a helper table that just kept running totals (which you could hide), then you can use the sort function to show that in decending order (by points). like so:

Arsenal45Wolves46Spurs39United46Chelsea50West Ham42Helper Table
positionTeamPointsTeamPoints
WatfordAWatfordHBrightonAWatfordHArsenalHWolvesH1=SORT(X3:Y8,2,-1)Arsenal=SUM(C:C)
LeicesterHArsenalAArsenalHMan CityALeicesterHLiverpoolA2Wolves=SUM(F:F)
LiverpoolHWest HamALeedsASpursHBurnleyAAston VillaH3Spurs=SUM(I:I)
Aston VillaACrystal PalaceHEvertonHLiverpoolANewcastleHSpursA4United=SUM(L:L)
Crystal PalaceAEvertonAMan UtdALeicesterHNorwichAEvertonH5Chelsea=SUM(O:O)
BrightonHLeedsHWest HamHEvertonABrentfordHBrentfordA6West Ham=SUM(R:R)
SouthamptonAAston VillaHNewcastleHNorwichHSouthamptonABurnleyH7
Manchester UnitedHNewcastleAAston VillaAArsenalALeedsAChelseaA8
West Ham UnitedAMan CityHBrightonHBrentfordHWest HamHArsenalH
LeedsHBurnleyABrentfordABrightonAEvertonANorwichA
Newcastle UnitedABrightonHLeicesterHChelseaHWolvesHMan CityH
EvertonHChelseaALiverpoolACrystal PalaceAMan UtdABrightonA
Tottenham HotspurANorwichHBurnleyHWatfordH
ChelseaALiverpoolANorwichA

That sort formula results in this table:

1645792278023.png
 
Upvote 0
@trunten the OP's profile shows they are using xl 2016 & therefore don't have the sort function. ;)
 
Upvote 0
?

similar approach though. try this instead ?

Helper Table
positionTeamPointsTeamPointsRank
1=IFERROR(INDEX($X$3:$X$8,MATCH($T3,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T3,$Z$3:$Z$8,0)),"")Arsenal=SUM(C:C)=COUNTIF($Y$3:$Y$8,">"&$Y3)+COUNTIF($Y$3:Y3,$Y3)
2=IFERROR(INDEX($X$3:$X$8,MATCH($T4,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T4,$Z$3:$Z$8,0)),"")Wolves=SUM(F:F)=COUNTIF($Y$3:$Y$8,">"&$Y4)+COUNTIF($Y$3:Y4,$Y4)
3=IFERROR(INDEX($X$3:$X$8,MATCH($T5,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T5,$Z$3:$Z$8,0)),"")Spurs=SUM(I:I)=COUNTIF($Y$3:$Y$8,">"&$Y5)+COUNTIF($Y$3:Y5,$Y5)
4=IFERROR(INDEX($X$3:$X$8,MATCH($T6,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T6,$Z$3:$Z$8,0)),"")United=SUM(L:L)=COUNTIF($Y$3:$Y$8,">"&$Y6)+COUNTIF($Y$3:Y6,$Y6)
5=IFERROR(INDEX($X$3:$X$8,MATCH($T7,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T7,$Z$3:$Z$8,0)),"")Chelsea=SUM(O:O)=COUNTIF($Y$3:$Y$8,">"&$Y7)+COUNTIF($Y$3:Y7,$Y7)
6=IFERROR(INDEX($X$3:$X$8,MATCH($T8,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T8,$Z$3:$Z$8,0)),"")West Ham=SUM(R:R)=COUNTIF($Y$3:$Y$8,">"&$Y8)+COUNTIF($Y$3:Y8,$Y8)
7=IFERROR(INDEX($X$3:$X$8,MATCH($T9,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T9,$Z$3:$Z$8,0)),"")
8=IFERROR(INDEX($X$3:$X$8,MATCH($T10,$Z$3:$Z$8,0)),"")=IFERROR(INDEX($Y$3:$Y$8,MATCH($T10,$Z$3:$Z$8,0)),"")
 
Upvote 0
thanks for your input - appreciated
trying to work out what the letters relate to, so i can update them on my table
 
Upvote 0
my fixtures table ran from A1:R16

Sorted table T2:V10 (including headers)

Helper table X1:Z8 (including headers)

those formulas all work on that basis. so would need to be tweaked to reference the areas on your sheet. I'll ee if i can do a simplified eg that you can copy/paste into excel
 
Upvote 0
This should paste nicely into a blank sheet (A1) so you can see what the formulas are doing :) Just need to then expand to your full fixtures table
Arsenal45Wolves46Helper Table
positionTeamPointsTeamPointsRank
WatfordA3WatfordH1=IFERROR(INDEX($L:$L,MATCH($H4,$N:$N,0)),"")=IFERROR(INDEX($M:$M,MATCH($H4,$N:$N,0)),"")Arsenal=SUM(C:C)=COUNTIF($M:$M,">"&$M4)+COUNTIF($M$4:M4,$M4)
LeicesterH1ArsenalA2=IFERROR(INDEX($L:$L,MATCH($H5,$N:$N,0)),"")=IFERROR(INDEX($M:$M,MATCH($H5,$N:$N,0)),"")Wolves=SUM(F:F)=COUNTIF($M:$M,">"&$M5)+COUNTIF($M$4:M5,$M5)
LiverpoolH1West HamA
Aston VillaACrystal PalaceH
Crystal PalaceAEvertonA
BrightonHLeedsH
SouthamptonAAston VillaH
Manchester UnitedHNewcastleA
West Ham UnitedAMan CityH
LeedsHBurnleyA
Newcastle UnitedABrightonH
EvertonHChelseaA
Tottenham HotspurANorwichH
ChelseaALiverpoolA
 
Upvote 0
Solution

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top