Leauge Table....Help!!!

[ronaldo444]

Hello my name is Ronnie. I am running a Premiership Predictascore game this football season and want to add the Premiership table to the workbook, so that once I have inputted the scores for that week into the spreadsheet, it automatically puts the information into the relevant box.

I have already the template for the table, the Goals for and against columns are set up, the points column also is set up but I can not work out what I need to do to get the information across.

For example, Arsenal win their first game of the season against Aston Villa 3-0, I need the sheet to insert a '1' into the home win column for Arsenal and a '1' in the away lost column for Aston Villa, and so forth.

Is there anyone out there who can help me? I am pulling what hair I have left out.....

[Dav]

The attached is a quick start, you will need to populate it with the fixtures as they take place, as it stands it will not like postponed fixtures


I would then Create a keystroke macro to sort the table by Pts the gD , but as I am sending you a spreadsheet decided not to include a macro, its easy to do

Regards

Dav

[ronaldo444]

Thanks mate.

[ronaldo444]

Dav
I meant to put into the previous post. I have the result page on a different worksheet, will that matter? Is the formula the same?

Cheers mate.

[Dav]

the formula is fundamentally the same but you are looking at data on a different sheet

so All the ranges $a$1:$a$1000 for example wil have to be name of data sheet!$a$1:$a$1000

eg sheet2!$a$1:$a$1000

I think I have changed everything in attached

Regards

Dav

[tankimpong]

Hi,the position can be sorted automatically base on the points each team get?

thanks

From,

Tan

[Dav]

I would just create a keystroke macros to sort by PTS descending then GD descending then Team ascending and then put a button on the worksheet next to the table and assign the macro to the button. Then press it after the new results have been inputted, otherwise it would sort after each result was added and this seems inelegant

A macro something like the following put in a module should work

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 12/07/2006 '

'
Range("B2:Q22").Select
Selection.Sort Key1:=Range("Q3"), Order1:=xlDescending, Key2:=Range("P3") _
, Order2:=xlDescending, Key3:=Range("B3"), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Regards

Dav

[tankimpong]

Thanks Dav,

It is great.
I found 1 more thing, when i delete all the score, means all results is blank. but the draw game is not 0. because blank for both team's score is consider draw. i tried many ways but cannot solve it. do you have any idea?

Thanks

Tan

[Dav]

Yes it was done in a rush, well spotted! you just need to add another condition to the sumproduct for the cell in K3

SUMPRODUCT(($A$1:$A$1000=$H3)*($B$1:$B$1000=$C$1:$C$1000)*(ISNUMBER($B$1:$B$1000)))

This can be copied down for the rest of the column, for home draws, a similar thing needs to happen for away draws

Regards

Dav

[ronaldo444]

Dav

I have copied the formula as is on your template and from your post about updating from another sheet but it still will not do it. I get a error message come up on the screen.

I have cut and pasted the sheets as I have set them out but not all of the fixtures as you can tell and attached them to this post. Can you help me?

[Dav]

I have ammended your table and added a button to sort the league table, which is why it may mention macros. the ranges in the table may all need to be lengthend if you have not included all the fixtures. I have made a guess at 600 rows which should be more than enough

Regards

Dav

[ronaldo444]

Cheers mate...

[ronaldo444]

That formula works fine but only when the range is set as you had it. However, I have the rest of the fixtures by month as you know but they goes along hte spreadsheet rather than down. When i put the range in ($B$5:$AB$140), it comes up with '###' and 'Value Not Available' Error. What am I doing wrong?

Also when I input a score into the first game of the season (Arsenal 4 Aston Villa 0), it doesn't put the scores into the table. I have cut and paste the formula over and changed the Sheetname but it still will not work......

[Dav]

Well the short answer is that you have arranged your data badly and everything in columns is definately preferable

If you really wanted the formulas to go that way as well , lets say the fixtures go for 4 lots accross, the formulas would be 4 times as long as you would need to repeat the sum product for each group

Eg for wins ar home =SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Sheet1!$C$5:$C$600>Sheet1!$E$5:$E$600))

would have to have+SUMPRODUCT((Sheet1!$g$5:$g$600=$A5)*(Sheet1!$h$5:$h$600>Sheet1!$i$5:$iE$600)) added to it and similarly additions for each group you go sideways for. I hope you can see it would be a pain. Also the longer the formula the more potential for error

Most lists of fixtures on the internet are all in one lot of columns, this is why its easier for five lots of columns the formula would be this long. not the correct formula, just to give you an idea of length
=SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Sheet1!$C$5:$C$600>Sheet1!$E$5:$E$600))+SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Sheet1!$C$5:$C$600>Sheet1!$E$5:$E$600))+SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Sheet1!$C$5:$C$600>Sheet1!$E$5:$E$600))+SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Sheet1!$C$5:$C$600>Sheet1!$E$5:$E$600))+SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Sheet1!$C$5:$C$600>Sheet1!$E$5:$E$600))

horrible

Regards

Dav