Announcement

Collapse
No announcement yet.

Automatically Sort Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 18:43.

  • #2
    Re: League Table Automatic Positioning

    Sort by the column in descending order?

    KJ

    Comment


    • #3
      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.
      Code:
      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, 18:42.

      Comment


      • #4
        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, 18:48.

        Comment


        • #5
          Re: Automatically Sort Table

          Use Sort.

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment


              • #8
                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!

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Automatically Sort Table

                      Thanks Ian, thats brilliant,



                      and to everyone whos helped me out , a massive thanks

                      Comment


                      • #12
                        Re: Automatically Sort Table

                        TI don't get it.

                        I watched your sheet. You can sort whatever you want. Perhaps there is just a mistake with team 17 (Q) which seems to have a total score of 22. when you sort by the the last column, Team Q with position 17 will of course appear on the 5th row.

                        However there is one mistake in your list. when building a list u should never leave a colum or row totally blank. One of your columns has no data. Dangerous if you work with large lists and do all sorts of filtering and sorting on them. Just delete that column

                        regards
                        Zion

                        Comment


                        • #13
                          Re: Automatically Sort Table

                          Freddie2007, please state your problem clearly 1st up, not a bit more with each post.

                          Comment


                          • #14
                            Re: Automatically Sort Table

                            i will do anymore, sorry

                            Comment

                            Working...
                            X