Sort Data using Formula

Hi, I am new to this Excel Forum.

I have loved playing around with excel for years and consider myself to be an above average user since everyone else I know either doesn't know a single formula or only knows =SUM

I absolutely love the World Cup and Euro competitions. I follow Football in general but I am most passionate about the World Cup.

With Euro 2016 closing in, I thought I would do a new excel doc. I have my existing World Cup 2014 Doc which has several formulas in it, however the one thing I can't seem to get excel to do without using a macro is getting the tables to sort the data. The idea behind the spreadsheet is to fill in the results of the teams that played and excel fills out the table (which works fine) however I then have to assign a sort macro assign the macro to a picture of the Jules Rimet Trophy. As the table changes, so does the team that qualifies for the Knockout Stage, however the table only changes because of the Macro and in my opinion the user of the spreadsheet shouldn't need to click the Jules Rimet Trophy everytime a match is played. Is it possible to fill out the results and then the table to sort itself automatically. I had thought about using the =LARGE(array,1) and so on until =LARGE(array,4) and then hide the original data from the user but this wouldn't sort the whole table, only the column with points in, and although I could use =LARGE for the other columns, it is still possible for a team to be on less points having scored more goals.

The columns I have currently are (from groupA WC2014):
Team Name (Brazil,Mexico,Croatia,Cameroon)
Played (=W+D+L)
Win, Draw & Lose Columns calcuted on hidden sheet
Goals For and Against are taken from where user inputs scores
Goal Difference (=Goals For - Goals Against)
and Last column is Points (=Win*3+Draws)

The only column that has data input directly is the Team Name. I wondered if there was a formula like =index or something that would allow excel to re-sort the data. All the data in the columns above could be hidden from view so that the user only sees the new sorted data. This could work using =LARGE but that would only work for points. A team could have the most points but less goal difference or goals scored. So if I was to change a game that Brazil played and make it so they lost instead of won, they would still be top of the group although they have less points, at least until I click the Macro.

The sort Macro I currently have is as follows:

Sort column Points by L to S
then Sort column Goal Difference by L to S
then Sort column Goals For by L to S

after this, normally, the competition would use the teams head to head results to decide who goes ahead of who but to be honest I don't think I need to include and complicate the formula to accomodate this because it almost never happens.

I had also wondered if it was possible to create a formula so that when the results are entered, or whenever any cell is changed, the macro runs in the background. Is any of this even possible? I do remember downloading someone elses World Cup 2010 back in 2010 and that did have it whereby the tables updated automatically, however upon studying their spreadsheet, I couldn't identify how they did it and wondered if perhaps they used VBA?

Thanks

Can be done with vba by firing your sort macro from a worksheet change / calculate event, or with formula using a method similar to the one below.

With unsorted numeric data (points, goal diff, goals for) in A2:C10, enter this formula into D2 and fill down,

=(RANK(A2,$A$2:$A$7)+(RANK(B2,$B$2:$B$7)/100)+(RANK(C2,$C$2:$C$7)/10000))*10000+RAND()

This will rank the data by column A, then by column B, then by column C

Then enter this into E2, copy right to G2, then fill down, this will show the data / scores sorted as required.

=INDEX(A$2:A$10,MATCH(LARGE($D$2:$D$10,ROWS(E$2:E2)),$D$2:$D$20,0))

Based on a small sample, which you should be able to expand to suit your needs.