Live updating football league table without filter

ronie85

Board Regular
Joined
Jan 25, 2014
Messages
101
Office Version
  1. 365
Platform
  1. Windows
I am wanting to know how to live update a league table so that I don’t have to do it manually with the filter. Team is in column ‘A’, GD is in Column ‘R’ and PTS is in column ‘S’. There are 25 rows including the header row. I was the table to update with 3 priorities.
First Priority is PTS in column ‘S’.
If any PTS are the same then I want the second priority to be GD in column ‘R’.
If any GD are the same I then want the third priority to be F in column ‘F’.
Is this possible??

[TABLE="width: 731"]
<tbody>[TR]
[TD]Team
[/TD]
[TD]Played
[/TD]
[TD]W
[/TD]
[TD]D
[/TD]
[TD]L
[/TD]
[TD]F
[/TD]
[TD]A
[/TD]
[TD]W
[/TD]
[TD]D
[/TD]
[TD]L
[/TD]
[TD]F
[/TD]
[TD]A
[/TD]
[TD]W
[/TD]
[TD]D
[/TD]
[TD]L
[/TD]
[TD]F
[/TD]
[TD]A
[/TD]
[TD]GD
[/TD]
[TD]PTS
[/TD]
[/TR]
[TR]
[TD]Bristol City
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]12
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]7
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]8
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]Peterborough
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]12
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]MK Dons
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]14
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]Chesterfield
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]Coventry
[/TD]
[TD]7
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]9
[/TD]
[TD]7
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Swindon
[/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]11
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
are u looking to rank the teams?


<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">T2</th><td style="text-align:left">=RANK(<font color="Blue">$S2,$S$2:$S$7,0</font>)+COUNTIFS(<font color="Blue">$S$2:$S$7,$S2,$R$2:$R$7,">" & $R2</font>)+COUNTIFS(<font color="Blue">$S$2:$S$7,$S2,$R$2:$R$7,$R2,$F$2:$F$7,">"&$F2</font>)</td></tr></tbody></table></td></tr></table><br />



Excel 2013
ABCDEFGHIJKLMNOPQRST
1TeamPlayedWDLFAWDLFAWDLFAGDPTSRanking
2Bristol City752124317121538161
3Peterborough7511126211753516163
4MK Dons6411148216221866162
5Chesterfield6411105216321425134
6Coventry7331207316321344125
7Swindon63211062162111444126
Sheet4
 
Upvote 0
If its a sort you are after id apply this code to a button on the worksheet.

Code:
Sub Sorty()

Dim sh As Worksheet

Set sh = ActiveWorkbook.Worksheets("Sheet1")

    sh.Sort.SortFields.Clear
    sh.Sort.SortFields.Add Key:=Range("S2:S25"), Order:=xlDescending
    sh.Sort.SortFields.Add Key:=Range("R2:R25"), Order:=xlDescending
    sh.Sort.SortFields.Add Key:=Range("F2:F25"), Order:=xlDescending
    With sh.Sort
        .SetRange Range("A1:S25")
        .Header = xlYes
        .Apply
    End With
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,269
Messages
6,177,564
Members
452,784
Latest member
talippo

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