Copy a column and sort...

[Tayta]

Guys

I have two columns. These are generated by a scoring system on a football league prediction calculator.

I end up with something like this.

Name Points
Dave 21
Anne 12
Bert 10
Jon 30

These values are a reference to another sheet i.e ='Running Total'!A2 for the left hand column and ='Running Total'!B2 for the right.

Now I want to sort these columns, but I want this to happen automatically. So if Anne has a great week and earns 50 points, she will automatically go to the top, and the others follow suit in order of most points to least.

Of course this is easy if I do a copy and sort every week...but is there a clever way?

Many Thanks

 

[Joe4]

When and how do these updates happen?

There a few ways you can do this. You could create a macro to automatically sort your list. You could then create a button to run this macro whenever clicked. Or you may be able to use an Event Procedure Macro, which is a macro that is automatically triggered upon some event happening (i.e. it could run every time the file is opened, etc).

Here is a link that talks about different Event Procedure options: http://www.cpearson.com/excel/Events.aspx

 

[Tayta]

Well I load the spreadsheet, enter some predicted scores. This then gets cross referenced to the actual scores leaving a value of x.

Then all of the x's are added up to give 'this weeks total'. Call it Y.

I then add Y to the TOTAL ONGOING SCORE which is Z.

When Z alters as the weeks go by, I want to be able to have a column that changes automaticaly (or with the click of a button) to put it in Winner order.

1st, 2nd, 3rd etc ongoing, to see it change. Like a league table.

 

[Joe4]

If you turn on the Macro Recorder and record yourself sorting the list, then you should have most of the VBA code you need to do the sort. Then, just attach that code to a button, and you can run it whenever you like by clicking the button.

 

[Tayta]

Ah yes I guess I copuld include the copy and paste bits in the recorded macro too! Excellent, i'll try that. Thank you