Macro to make League Table update automatically as scores are entered

Lmaonade

Board Regular
Joined
Jan 5, 2018
Messages
52
Hi,

I've created this table as an example.

What I would like to do is get this table to automatically sort the players in order as scores are entered on another sheet. The formulas in the table are already set to update automatically with the wins, losses, points etc. But I always need to press to sort out after these scores are entered rather than it just sorting on it's own.

So what I would like is a way that the table automatically puts whoever has the highest "Points" at the top followed by the highest "Diff". At the moment this is just done with a custom sort after each score is entered.

1590653593236.png


Appreciate any help.

Kind regards,
Andy
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
The way I do this on all the league scenarios I have is keep an unsorted table in a work area and add an extra 2 helper columns first one we use to build tie breaker criteria so points then diff then whatever else you want to split ties with so you can then RANK on this to produce a unique number
on the presentation view of the table in another area or sheet I use INDEX and MATCH to look up the ranking number, 1,2,3 etc and pull all the data I require this makes the league table dynamic as results are added
 
Upvote 0
I'm not sure how to use INDEX and MATCH.

So what would I need to do here in the top table to get the right data in the right order automatically as that lower table is updated?

1590669538526.png


Thanks,
Andy
 
Upvote 0
sorry in delay had to type stuff into excel, its best to use the XL2BB routine saves solution provider typing stuff in, okay here goes
to you bottom table add an extra column called tiebreak containing points + (500 + diff)/10000, and beside that another column for ranking, this assumes no duplicates in the previous column otherwise we have to further extend the tiebreak formula

Book1
ABCDEFGHIJKLMNO
10PlayedWonLostHome (W)Home (L)Away (W)Away (L)ForAgainstDiffPointsTieBreakerRank
111Player 1211011098139-4133.04594
122Player 22201-10226910016966.06691
133Player 32020200104170-6600.04345
144Player 421110015061-1133.04893
155Player 52202-20298158366.05832
166Player 62020200100234-13400.03666
Sheet1
Cell Formulas
RangeFormula
N11:N16N11=M11+(500+L11)/10000
O11:O16O11=RANK(N11,$N$11:$N$16,0)


now in the top table we have to pick up the RANK column to drive the leader board
so in top table under players names we insert =INDEX(B$11:B$16,MATCH($A3,$O$11:$O$16,0)) where B11:B16 is bottom table names, and O11:O16 is bottom table ranking, notice the use of dollar signs to anchor the ranges to stop them moving so when we drag down and across nothing but the required bits move in the formulae
after you have inserted the above, drag down to where you want leader6 to go then drag across and all data will be dynamic, then play with numbers in bottom table and watch the leader board update

Book1
ABCDEFGHIJKLM
2PlayedWonLostHome (W)Home (L)Away (W)Away (L)ForAgainstDiffPoints
31Player 22201-1022691001696
42Player 52202-2029815836
53Player 421110015061-113
64Player 1211011098139-413
75Player 32020200104170-660
86Player 62020200100234-1340
Sheet1
Cell Formulas
RangeFormula
B3:M8B3=INDEX(B$11:B$16,MATCH($A3,$O$11:$O$16,0))
 
Upvote 0
Jim, That worked absolutely perfectly. Now it's all set up I just hid the rows for the lower table and now when scores are entered everything updates on it's own. Perfect.

Really grateful for that! :)

Kind regards,
Andy
 
Upvote 0
@Lmaonade
What version of Excel are you using?
If you modify your account details, you can show what version/platform you use, which helps members tailor a suitable response.
 
Upvote 0
@Lmaonade thanks for the feedback
In my solutions I try to use simple formulas maybe using helper columns to try and explain the concept rather than a big complex formulas which only confuse the issue
i have many spreadsheets for various sporting events, competitions and tournaments that use the same methodology
the only fly in the soup is when you have identical rankings should this occur then you have to add another criteria to the tiebreak column bearing in mind for each one you add you have to divide by an increasing factor of 1000‘s to get the correct number of decimal places also you will note I have used 500 you may have to increase this If your numbers in the difference column get huge this is only used when you have positive and negative numbers to factor in, if it was only FOR then you wouldn’t need the 500 just the straight value
there are many ways of achieving the ranking using RANK or SUMPRODUCT or other ways
 
Upvote 0
If you have 365 with dynamic arrays, you could use

+Fluff New.xlsm
ABCDEFGHIJKLMN
1PlayerPlayedWonLostHome (W)Home (L)Away (W)Away (L)ForAgainstDiffPoints
21Player 22201-1022691001696
32Player 52202-2029815836
43Player 421110015061-113
54Player 1211011098139-413
65Player 32020200104170-660
76Player 62020200100234-1340
8
9
10PlayerPlayedWonLostHome (W)Home (L)Away (W)Away (L)ForAgainstDiffPoints
111Player 1211011098139-413
122Player 22201-1022691001696
133Player 32020200104170-660
144Player 421110015061-113
155Player 52202-2029815836
166Player 62020200100234-1340
17
Lookup
Cell Formulas
RangeFormula
B1:M7B1=SORTBY(B10:M16,M10:M16,-1,L10:L16,-1)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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