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.....


--
ronaldo444
------------------------------------------------------------------------
ronaldo444's Profile: http://www.excelforum.com/member.php...o&userid=36233
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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


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

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

Thanks mate.


--
ronaldo444
------------------------------------------------------------------------
ronaldo444's Profile: http://www.excelforum.com/member.php...o&userid=36233
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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.


--
ronaldo444
------------------------------------------------------------------------
ronaldo444's Profile: http://www.excelforum.com/member.php...o&userid=36233
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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

thanks

From,

Tan


--
tankimpong
------------------------------------------------------------------------
tankimpong's Profile: http://www.excelforum.com/member.php...o&userid=31248
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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


--
tankimpong
------------------------------------------------------------------------
tankimpong's Profile: http://www.excelforum.com/member.php...o&userid=31248
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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?


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

--
ronaldo444
------------------------------------------------------------------------
ronaldo444's Profile: http://www.excelforum.com/member.php...o&userid=36233
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172

Cheers mate...:)


--
ronaldo444
------------------------------------------------------------------------
ronaldo444's Profile: http://www.excelforum.com/member.php...o&userid=36233
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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?


--
ronaldo444
------------------------------------------------------------------------
ronaldo444's Profile: http://www.excelforum.com/member.php...o&userid=36233
View this thread: http://www.excelforum.com/showthread...hreadid=560172

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$600Sheet1!$E$5:$E$600))

would have to
have+SUMPRODUCT((Sheet1!$g$5:$g$600=$A5)*(Sheet1!$ h$5:$h$600Sheet1!$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$600Sheet1!$E$5:$E$600))+SUMPRODUCT((Sheet1!$B$ 5:$B$600=$A5)*(Sheet1!$C$5:$C$600Sheet1!$E$5:$E$6 00))+SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Sheet1!$ C$5:$C$600Sheet1!$E$5:$E$600))+SUMPRODUCT((Sheet1 !$B$5:$B$600=$A5)*(Sheet1!$C$5:$C$600Sheet1!$E$5: $E$600))+SUMPRODUCT((Sheet1!$B$5:$B$600=$A5)*(Shee t1!$C$5:$C$600Sheet1!$E$5:$E$600))

horrible

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=560172