Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel help

Options
  • 08-04-2014 10:22pm
    #1
    Registered Users Posts: 50 ✭✭


    Hi,
    Im having trouble with excel im trying to make a world cup league table and so far its going well but i have no way of it auto sorting so that the team with the most points goes to the top also i have a function in that if there is a draw it changes the team stats to have one draw one game played and 1 point because they equal eachother before you type anything in is there anyway to delay this? thank you :)
    Tagged:


Comments

  • Registered Users Posts: 7,157 ✭✭✭srsly78




  • Registered Users Posts: 50 ✭✭spudato7


    thanks for the reply but im not looking for a timer its more that I want it to update when I type it in


  • Registered Users Posts: 7,157 ✭✭✭srsly78


    http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change

    Worksheet_Change event will fire when a cell has been changed.
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Me.Range("H5")) Is Nothing Then DoSomething()
    End Sub
    
    


  • Registered Users Posts: 50 ✭✭spudato7


    thanks for th reply do you know how to set the table to automatically put the team with the most points to the top?


  • Registered Users Posts: 7,157 ✭✭✭srsly78




  • Advertisement
  • Registered Users Posts: 50 ✭✭spudato7


    i know but i was looking for someone to put it simpy for me because im not too advanced


  • Closed Accounts Posts: 3,009 ✭✭✭sopretty


    Just select the entire table by highlighting it and click on the Data Sorting tool on the menu on top. Select the column you wish to sort by (i.e. the scores) and sort it either ascending (ranging from lowest value to highest value, or Descending, if you wish the highest value to appear at the top).


  • Registered Users Posts: 7,157 ✭✭✭srsly78


    I assume the op wants to do it programmatically through vba, not by clicking on column.


  • Registered Users Posts: 50 ✭✭spudato7


    ive been doing what sopretty has said but i want it to happen when i change the value so i dont have to resort it everytime


  • Closed Accounts Posts: 3,009 ✭✭✭sopretty


    srsly78 wrote: »
    I assume the op wants to do it programmatically through vba, not by clicking on column.

    Why did ya have to complicate things lol. Didn't realise what forum this was in. Was just browsing thread titles. :P


  • Advertisement
  • Closed Accounts Posts: 1,822 ✭✭✭Chazz Michael Michaels


    spudato7 wrote: »
    ive been doing what sopretty has said but i want it to happen when i change the value so i dont have to resort it everytime

    Why not?


  • Closed Accounts Posts: 3,009 ✭✭✭sopretty


    spudato7 wrote: »
    ive been doing what sopretty has said but i want it to happen when i change the value so i dont have to resort it everytime

    If you change a figure in a field which is involved in a formula, the formula should update the field in which the formula is in.

    Do it simply

    Column A : Enter digit 2
    Column B: Enter digit 4
    Column C: Enter formula = A+B (depending what cells you're in).

    Then change a figure in either columns A or B and see whether C updates?


  • Closed Accounts Posts: 3,009 ✭✭✭sopretty


    Just to mention, you'll have to click out of a cell for it to update.


  • Banned (with Prison Access) Posts: 32,865 ✭✭✭✭MagicMarker


    sopretty wrote: »
    If you change a figure in a field which is involved in a formula, the formula should update the field in which the formula is in.

    Do it simply

    Column A : Enter digit 2
    Column B: Enter digit 4
    Column C: Enter formula = A+B (depending what cells you're in).

    Then change a figure in either columns A or B and see whether C updates?
    The OP says he wants the table to sort automatically when values are changed. What are you replying to?


  • Closed Accounts Posts: 3,009 ✭✭✭sopretty


    The OP says he wants the table to sort automatically when values are changed. What are you replying to?

    Oh. I thought he wanted the field to update. My mistake.


  • Registered Users Posts: 59,610 ✭✭✭✭namenotavailablE


    The demo file available at this link sounds like it does the general idea of what you want to do:

    https://app.box.com/s/j0diridxw857xv9sib7h


  • Registered Users Posts: 9 Avantech


    Hi Spudato,

    The most straight forward way to do it is to record a macro that sorts the list as you want. Then go to the VB editor and have a look in the Module(should be Module1). Here you will find the code for the recorded macro. Copy this code out into the Microsoft Excel Objects section (for the sheet you are changing).

    Paste the code in here but surround it with (Don't copy the initial opening Sub command - example below or closing Sub Command)
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
    .
    .
    .
    .
    .
    .
    .
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    End Sub
    

    So you will have something like
    Private Sub Worksheet_Change(ByVal Target As Range)
    Range("B1:C6").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C6"), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("B1:C6")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    End Sub
    

    Now when you change any value on the sheet this macro will be fired and the table will update as you want.

    Regards,
    Avantech


Advertisement