| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 37983 | 2003-09-23 22:05:00 | Excel Auto sort | Peter H (220) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 177426 | 2003-09-23 22:05:00 | Any way to get Excel to Auto sort columns? Doesn't appear in the help file. Bye |
Peter H (220) | ||
| 177427 | 2003-09-23 22:39:00 | What exactly are you trying to do Peter? Are you trying to sort a column of numbers into ascending or descending order? |
B.M. (505) | ||
| 177428 | 2003-09-24 03:46:00 | Thanks for the interest. It is a entry sheet - split into 4 groups for results. The entry sheet has scores entered, and this is linked to the result sheets. At the end, I manualy sort using the sort fuction. What I would like, is as results are entered, the function be automatic, so at a glance you can see the top score on each sheet. At the moment, you have to wait for all scores to come in and then sort. Hope this makes sense. Bye |
Peter H (220) | ||
| 177429 | 2003-09-24 05:18:00 | Hi Peter, there is no Auto sort function that I am aware of but it's certainly possible to do this with a dynamic macro. If you want I can write one for you but I would need to know what the columns are in the whole range and the order of sort (eg data in cols a-h, with ascending sort on col d). hth Parry |
parry (27) | ||
| 177430 | 2003-09-24 06:03:00 | Thanks Parry. Data is approx 120 rows - the contest is held over 2 days with day 1 results in col F, day 2 col G, and totals in col H. This master sheet is linked to 4 sheets for handicap groups, and 3 sheets for age groups. Only sort needed is Column G in all 7 sheets - not the master, as I need to keep this order. Sort will be descending by numbers, the highest would be about 90. Bye |
Peter H (220) | ||
| 177431 | 2003-09-24 07:11:00 | Hi Peter, see the macro below. I have made the assumption you have a header row in row 1, so the code will sort all rows in columns f:h from row 2 downwards. The macro is linked to the change event meaning any change in any cell will run the macro. However, it will only sort after someone changes a cell in row H. I suggest you do a test first by copying the workbook and running the code on that as macros cannot be "undone" - the undo button wont do anything. To copy the code do the following:- 1) Open the VB Editor (Alt-F11) 2) You will see its split into 2 windows. In the left hand side under the folder Microsoft Excel Objects you will see each of your sheets listed. Double click on the sheet. 3) In the right hand side paste the code (starting from line Private Sub... until End Sub). You will need to repeat steps 2-3 for each sheet. Private Sub Worksheet_Change(ByVal Target As Range) 'Check that cell changed was in col H. If not, exit. If Target.Column <> 8 Then Exit Sub 'Sort cells f:h by col g descending. Presumes header in row 1 Range("F:H").Sort Key1:=Range("g1"), Order1:=xlDescending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub |
parry (27) | ||
| 177432 | 2003-09-24 08:27:00 | Thanks Parry - will give it a go, but will be tomorrow. Weather doesn't look too good for Golf !! Bye |
Peter H (220) | ||
| 1 | |||||