Auto Sorting an F1 league table

W

woodzy58

New Member
Joined
Feb 24, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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.

TSD TABLE NEW 2021.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1135
2230
3GAMERTAGpenaltyXTRA PTS FOR327
412345678910111213141516171819202122FASTEST LAPTOTAL PTSPTS DIFF424
511ERN MasseyN/aN/a224117572133483710522
622Grit86 (allan)4597DNF538725396731320-51620
733raratron8923116741031144106595316-4718
844Xi Rashy105448964DNF4113DNFDNF43DNS3259-57816
955RRF1 R3DM4N668131011DNF4133DNF51111107217-42914
1066SF1 Goomba87510DNF1066DNF109DNS10769210-71012
1177SyKo SwishDNS1643DNSDNSDNS3DNSDNFDNSDNSDNS1DNF2170-401110
1288PRF1 CLEMMN/aN/aN/aN/a6DNSDNSDNF9568128541164-6129
1399XRF1 Rodge TN/aN/aN/aN/aN/aN/aN/aTrial867689116134-30138
141010ERN BigGN/aN/aN/aN/aN/aN/aN/aN/aN/aN/a114210821123-11147
151111C11 MRDALBYN/aN/aN/aN/a1295510DNSDNS2DNSDNF12DNS118-5156
161212Gamerman8827939DNFDSQDNF1312810159121114DNS14DNS112-6165
171313NutmegTuchasN/aN/a1412DNF137912810DNF13DNF13DSQ109-3174
181313THE GUNNERS0510917DNF14DNF911161313DNSDNF1215101090183
191415Re1 iCz1DNSDNSDNS2DNFDNSDNS14DNSDNSDNSDNS1DNSDNS107-2192
201516TIR DEANN/aN/aN/aN/aDNFDNSDNSDNSDNSDNS1DNF1DNSDNSDNS272-35201
211617Bekker313DNF8151111DNSDNFDNSDNS12DNF9DNFDNSDNFDNF65-7DNF0
221718LuckyThirteen91DNSDNS7DNSDNSDNSDNFDNSDNSDSQDNSDNS5DNFDNSDNS40-25DNS0
231819PRF1ViPeRN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/a135-5N/a0
241920Gn0PENTAN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/areserveDNS0-35reserve0
251920VRF1 GetsugaN/aN/aN/aN/aN/aN/aN/aN/areserveDNFDNSDNSDNSDNSDNSDNF00
261920VRF1 SennaN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/areservereserve00
271920Woodzy1991N/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/aN/a00
28
Sheet1
Cell Formulas
RangeFormula
AG5:AG27AG5=SUMPRODUCT(SUMIF(AK:AK,J5:AE5,AL:AL))+AF5
AH5AH5=SUM(AG5-AG5)
AH6:AH27AH6=SUM(AG6-AG5)
C5:C27C5=RANK(AG5,AG$5:AG$27)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Try a sheet level macro:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4:AG27")) Is Nothing Then

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "AG5:AG27"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C4:AG27")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End If
End Sub
 
Upvote 0
mrshl9898 said:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C4:AG27")) Is Nothing Then ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _ "AG5:AG27"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("C4:AG27") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End If End Sub
Click to expand...

Hi mrshl9898

I have tried to use your code but as soon as I add data into the main table I get the following error:
tsd_error.png


Are you able to assist?

Thank you!
 
Upvote 0
No worries, I've figured it out. If I remove the '2' from SortFields.Add2 and make it just SortFields.Add it works perfectly.

Big Thank you!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Auto Sorting League Table
Replies
2
Views
701
arun_eagertolearn
A
A
  • Question Question
making a bowling league table
Replies
3
Views
2K
iyyi
I
E
  • Question Question
Auto Sort League Table
Replies
0
Views
1K
ExcelRoy
E
S
  • Question Question
Auto sort a league table
Replies
3
Views
5K
Yogi Anand
Y

Forum statistics

Threads
1,224,830
Messages
6,181,228
Members
453,025
Latest member
Hannah_Pham93

Share this page

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