Leauge Table....Help!!!

[ronaldo444]

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

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

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

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

 

[Dav]

The attached is a quick start, you will need to populate it with th
fixtures as they take place, as it stands it will not like postpone
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

Da

+-------------------------------------------------------------------
|Filename: Leaguetable.zip
|Download: http://www.excelforum.com/attachment.php?postid=5008
+-------------------------------------------------------------------

 

[ronaldo444]

Thanks mate

 

[ronaldo444]

Dav
I meant to put into the previous post. I have the result page on
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
different sheet

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

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

I think I have changed everything in attached

Regards

Da

+-------------------------------------------------------------------
|Filename: Leaguetable1.zip
|Download: http://www.excelforum.com/attachment.php?postid=5010
+-------------------------------------------------------------------

 

[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


+-------------------------------------------------------------------+
|Filename: Leaguetable1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5010 |
+-------------------------------------------------------------------+

 

[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 place it on the worksheet next to the
table assigned to a button. Then press it when 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

 

[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

 

[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 pos
about updating from another sheet but it still will not do it. I get
error message come up on the screen.

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

+-------------------------------------------------------------------
|Filename: Test.zip
|Download: http://www.excelforum.com/attachment.php?postid=5026
+-------------------------------------------------------------------

 

[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


+-------------------------------------------------------------------+
|Filename: Test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5032 |
+-------------------------------------------------------------------+

 

[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 the
goes along hte spreadsheet rather than down. When i put the range i
($B$5:$AB$140), it comes up with '###' and 'Value Not Available' Error
What am I doing wrong

 

[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