Excel Football Prediction competition
Grizz
I am running a small competition at work (30-40 players)
for the forthcoming 2004-2005 English Football Premier League. Basically the
entrants have to predict the scores of each game and they receive points for
correctly guessing an Away Win (1 point), Home Win (2 points), Score Draw (1
point), No Score Draw (2 points) and then extra points (5 points) if the
score is completely correct.
I have a sheet for each player (Player Name) where their prediction is
posted and an identical sheet (Results) where the actual results are entered
as they happen. I have it so that each Player Sheet references the single
Results sheet and I have formulas that give points, as above, to each player
automatically depending on what the actual score was. Each person's points
are then totalled and the results sent to a sheet titled Leader Board. The
Leader Board sheet also shows, as well as the player's total score, the
number of H/Wins, A/Wins etc they have got todate. I then use Data-Sort to
put the leader board in order. In effect the football results are entered on
the one sheet and it updates automatically the player's scores, with just
the Data-Sort done manually
What I would like to do is to have an automatically updated and sorted
Premier League table for the football teams showing how many H/Wins, A/Wins,
Points, Goal Difference etc they have. There is a file on Microsoft's Excel
Templates download page that was done for the Euro 2004 Championship,
http://office.microsoft.com/templates/category.aspx?CategoryID=CT063459441033&CTT=4&Origin=CT062100621033
(topmost file) which achieves my goal (sic), the only problem is I'm not
totally sure how it's been done. I'm guessing it uses a pivot table in some
way (I'm not familiar with pivot tables very much) to work out from the
entered football results the Points, Goal Diff etc and then to sort the
table to put the team with the highest points at the top.
If you'd care to download the file and perhaps tell me how it's been
done It would be appreciated. I could then use the same procedure, I think,
to sort the competition's Player Leader Board table in the same way
(automatically). I am trying to minimise the amount of manual input/sorting
in the file to just entering the player's predictions and the actual game
results.
Any help with this would be very much appreciated,
TIA,
Grizz
Daniel.M
< snip >
> Points, Goal Difference etc they have. There is a file on Microsoft's Excel
> Templates download page that was done for the Euro 2004 Championship,
>
http://office.microsoft.com/templates/category.aspx?CategoryID=CT063459441033&CTT=4&Origin=CT062100621033
> (topmost file) which achieves my goal (sic), the only problem is I'm not
> totally sure how it's been done. I'm guessing it uses a pivot table in some
> way (I'm not familiar with pivot tables very much) to work out from the
> entered football results the Points, Goal Diff etc and then to sort the
> table to put the team with the highest points at the top.
>
1.You can unprotect the file to look at formulas.
2.You can also UnHide the columns that have been hidden.
3.You can go in the VB Editor (Alt-F11) and make the 2 hidden sheets visible (F4
to show properties, change Visible to xlSheetVisible)
4.You can use the Audit toolbar to find what cell is a dependent/precedent of
others.
The sheets structure (IMO, it sucks even though the layout is nice) will then be
available to you. You now should understand how _you_ can do the digging. :-)
Regards,
Daniel M.
daryl
microsoft's website any more. thanks!