How to "sort" league tables automatically based on changing forecasts

**Gooford** · 05-08-2010, 06:26 AM

Hi,

I have attached forecasting tool which I am working on for the world cup for the sake of practicing Excel.

The user enters the forecast result (W / L / D) in the column highlighted in yellow. This then gives a point score.

Currently using SUMIF I am totalling scores next to team names, but I would like to be able to then sort this automatically, ie if I change a forecast so that a team scores more or less, it moves up or down the table accordingly without the need to go through the manual sorting process.

Any help would be much appreciated!

PS I am using a different computer so I am in Excel 2007

**contaminated** · 05-08-2010, 07:10 AM

Hi
Take a look at attached. I obtained this formula from DO...

**Marcol** · 05-08-2010, 07:30 AM

Here's a VBa way also

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim isect As Range
    
    Set isect = Application.Intersect(Target, Range("N6:N9"))
    If Not isect Is Nothing Then
        Range("M6:N9").Sort Key1:=Range("N6"), Order1:=xlDescending
        Exit Sub
    End If

    'add code for each range as above (It's all done in example sheet)

End Sub

See attached file