Automatic sort of table into rank order when a new record is added

[tbones]

Hi all, I have been using a table that I input figures onto. What I want to be able to do is to have the rows move in order of rank automatically as new data is inputted.
So say I have 10 columns and say 10 rows and I want to add data to these rows I then want the table to sort in order of largest to smallest of say column 8 .

I have been using a VBA code that will do this if I double click on the header row however I dont want to have to do this and I want it to move the rows in order once the data has been inputted.

Here is the VBA I use so that I can click on the header to sort.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("A1:FE25").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
Range("A1:FE25").Sort Key1:=KeyRange, Header:=xlYes
End If
End Sub

Any help to be able to do the same as the code but without having to double click on the header column to sort would be appreciated.

 

[kevin9999]

Oh wow... that works as I wanted it to do so I can only but thank you for your help.
The way I will be populating the table is using different gameweek sheets which I will link to each player. What I do then is to manually input everyones predictions and then once the games have been played i then input the actual scores and it will populate the whole table based on the players predictions. So in essence it works perfectly. Thank you

Happy to help & thanks for the feedback

 

[tbones]

I do have another question around the worksheet. In gameweek 1 I have added some blank cells and with the whole column I use this formula =IF(AND(B8=H8,E8=K8),1,"0") which basically gives a score of 1 if the data in the range has a value. However when I add blank cells I want the the score to be blank until I add data . I tried using this formula =IF(IF(AND(B8=H8,E8=K8),1,"0"),"") however this works perfectly for blank data but it produces and error if I put in data as it does not understand the argument. I realise there is something missing like what if it has data but I am not sure how to put this into the formula

 

[tbones]

Sorry I did not say that right
I do have another question around the worksheet. In gameweek 1 I have added some blank cells and with the whole column I use this formula =IF(AND(B8=H8,E8=K8),1,"0") which basically gives a score of 1 if the data in the range has the same value. However when I add blank cells I want the the score to be blank until I add data . I tried using this formula =IF(IF(AND(B8=H8,E8=K8),1,"0"),"") however this works perfectly for blank data but it produces and error if I put in data as it does not understand the argument. I realise there is something missing like what if it has data but I am not sure how to put this into the formula

 

[kevin9999]

Try this:

=IF(AND(H8<>"",I8<>""),IF(AND(B8=H8,C8=I8),1,"0"),"")

 

[tbones]

Try this:

=IF(AND(H8<>"",I8<>""),IF(AND(B8=H8,C8=I8),1,"0"),"")

Thank you but it did not quite work as it distorted the values of the cells with data . If you go to the sheet I linked you with then go onto gameweek tab you will see what i mean.

 

[kevin9999]

I really need to know which cells you're putting the formula in, which cells you're leaving blank to test, and which cells are giving the incorrect results.

 

[tbones]

So if you look at the gameweek I have names of teams under gary Robertson. At the moment there are 10 games however if there are more than 10 games i want to be able to add the teams playing in column g and j and these will populate in the columns A and D. Once this is done then the scores and outcomes that the player predicts will go into column B,Cand E.
The formula then looks at column H and K and if the prediction is the same as the actual then they score 1 point... I have already populated that formula however the issue I have is that when the cells are blank they match the cells of actuals so give a score of 1 where I want it to be blank.
Does that make sense?

 

[kevin9999]

I think I can follow it. Try this (I'm using row 8 as the test)

=IF(OR(A8="",D8=""),"",IF(AND(B8=H8,C8=I8),1,"0"))

 

[tbones]

Great thank you... that seems to work ok.
Thank you for your time on this it is really appreciated. I only formulate Excel every now and then I I forget some simple functions. What i do understand is what i want to do and when I write it down to create the formula I seem to confuse myself. lol.
Thanks once again.

 

[kevin9999]

You're more than welcome, and any time you need a tip - look no further than the Mr Excel forum