Auto Sorting an F1 league table

[woodzy58]

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
	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)

 

[mrshl9898]

Try a sheet level macro:

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

 

[woodzy58]

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

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:

Are you able to assist?

Thank you!

 

[woodzy58]

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!!

 

[mrshl9898]

No problems, glad you found it! Sorry there was a bit of bad code in there.