I like it when this happens: out of the blue someone says, can you do this? I always say, let’s have a look.
I opened an email today, from Dennis, which asked me if I could prepare a spreadsheet to automate the recording of the scores for a football tournament to be held very soon: the UK Africa Cup, with the final being played at Upton Park.
I finished work a bit early today and had a little bit of spare time so I took a look at the fixture list:
- four groups
- four teams in each group
- four quarter finals
- two (surprise!) semi finals
- a 3rd place play off
- a final
At first I thought, this is very complex but as I walked about a bit I thought … if I create a few helper areas, create league tables … use =RANK() rather than sorting each table. Then I can use =VLOOKUP() to go and find the teams for the quarters. ==IF()+IF()+IF()+IF() will give me the games won, drawn and lost … =SUM() will tell me how many games were played …
The work flowed fairly easily but it still took me about three hours to finish it. The best part was how I was able to get half way through the exercise and suddenly realise that the way they had organised the group stages, all I had to do to finish the spreadsheet was to copy and paste groups C and D into a copy of the work sheet I had created for groups A and B … then put them back together again …
After that it was just a matter of getting the look and feel of it right. I think I did that.
I teased Dennis at the half way stage by saying, you could finish it yourself now … ten minutes later I sent him the finished article as he said it was a bit complicated for him.
I have to say I enjoyed the job and felt quite satisfied as I tested it and found that it seems to have come together well. I know there are a couple of things that could be done to enhance it even further but what the heck, they are not needed!
Here are some screen shots for you! In reverse order …