Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Automatically Sort Table

  1. #1
    Join Date
    21st November 2006
    Posts
    7

    Automatically Sort Table

    I am currently in the process of creating a league table using Excel, and was wondering how I could get the teams to automatically arrange themselves in order of total points?, without using macros, is any formula capable of this?

    Thanks,
    Freddie
    Last edited by Freddie2007; November 21st, 2006 at 17:43.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd October 2006
    Location
    Cardiff, UK
    Posts
    263

    Re: League Table Automatic Positioning

    Sort by the column in descending order?

    KJ

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,696

    Re: League Table Automatic Positioning

    Just sort the table by the required column.

    Or, create a Dynamic Named Range called MyTable and use the Worksheet Change Event Code below. It assumes you wish to sort by the 1st column in your table.
    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If Target.Cells.Count > 1 Then Exit Sub 
         
        If Not Intersect(Target, Range("MyTable")) Is Nothing Then 
            With Range("MyTable") 
                .Sort Key1:=.Cells(1, 2), Order1:=xlDescending, Header:=xlGuess, _ 
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
                DataOption1:=xlSortNormal 
            End With 
        End If 
         
    End Sub 
    
    
    Last edited by Dave Hawley; November 21st, 2006 at 17:42.

  4. #4
    Join Date
    21st November 2006
    Posts
    7

    Re: Automatically Sort Table

    yeah, I want to sort by the column in descending order, how do I do that?
    Last edited by Freddie2007; November 21st, 2006 at 17:48.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,696

  6. #6
    Join Date
    21st November 2006
    Posts
    7

    Re: Automatically Sort Table

    Thanks, I did that and was able to sort the final column, the total points column, in descending order, but how can I set it to sort the first column which contains every teams position in the league, numerically also, as every time I try to sort it, it messes up the total points column.

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    3rd October 2006
    Location
    Cardiff, UK
    Posts
    263

    Re: Automatically Sort Table

    Select all the data i.e. both the teams and the scores (columns) and then Sort by the scores. It will then ensure that the data remains 'lined-up' if you see what I mean.

    KJ

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    21st November 2006
    Posts
    7

    Re: Automatically Sort Table

    Thanks for your help guys but I just cant get it to work, I've tried the rank formula but I keep getting back error messages, does anyone know what formula I could use?, there are 25 teams, the total points column is column I, and I want it ranked in ascending order, help me!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    11th June 2006
    Location
    Derbyshire UK
    Posts
    115

    Re: Automatically Sort Table

    Freddie2007

    Can you post your sheet, this will make it easier to understand why the sort and rank are not working for you.

    Ian

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    21st November 2006
    Posts
    7

    Re: Automatically Sort Table

    Yeah heres my league prototype attatched

    Thanks a lot if you get the time to take a look at it and help me out
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Automatically Sort As Data Added
    By stephen1980 in forum EXCEL HELP
    Replies: 3
    Last Post: June 26th, 2008, 10:06
  2. Automatically Sort Ranges On Calculation
    By Garyhunt6809 in forum EXCEL HELP
    Replies: 3
    Last Post: February 6th, 2008, 10:01
  3. Automatically Sort New Data
    By CGOOD in forum EXCEL HELP
    Replies: 2
    Last Post: January 6th, 2007, 09:25
  4. Sort Automatically
    By dredbluz in forum EXCEL HELP
    Replies: 2
    Last Post: March 30th, 2005, 22:52
  5. Charts - Automatically sort data
    By jacknijssen in forum EXCEL HELP
    Replies: 4
    Last Post: March 18th, 2005, 23:38

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno