Maintaining league tables is a frequent pastime of many of use, whether it be for local leagues that we participate in, for leagues that we run for our siblings, or just for the pleasure of holding and seeing the data and the results.

It can become quite onerous to maintain the results, repeatedly re-building the league tables. This is exactly the sort of small application that computers generally, and Excel specifically, lends themselves to. The two 'xld League Table' workbooks given here provides simple to use, fully-functional, ready to use solutions that help make life simpler (and leave you more time for the actual games).

The page is structured as follows

Basic Principles
Results
League Table
Maintenance
A Non-VBA Solution
In Summary
Download

Basic Principles

The workbook consists of two worksheets, a results worksheet and a league table worksheet, and maintenance menu.

As stated above, it can be used for soccer tables, rugby, basketball, chess, any activity that is based upon teams playing each other in a round-robin system. My example is based upon soccer, and specifically the English Premiership, but it can be tailored. The teams can be changed using the maintenance function to whatever teams you want for your league.

Results

The results spreadsheet is the heart of the workbook, this is where the results of games playeed are entered. Fig. 1 below shows this spreadsheet, with some scores already entered.

Fig. 1

The home teams are shown down the left, the away teams are shown acroos the top. The results are entered in the individual cells at the intersection of the home and away team, and entered in the format of home score, dash, away score (e.g. 2-1). There is no need to worry about the input being interpreted as a date, as the cells are formatted as text.

Notice the light yellow stripe in 'Manchester United' row and column. This is a presentational facility that highlights that team's fixtures.

One other presentational facility that is not shown in this picture is the active fixture, which is displayed in the status bar. For instance, in Fig.1, G15 is the active cell, so the 'Manchester United v Charlton Athletic' fixture is shown on the status bar. This is helping to ensure that the result is entered in the correct cell.

League Table

The league table spreadsheet is the second part of this workbook, and shows the relative positions of all of the teams, and their statistics for this one season.

The league table spreadsheet is automatically updated upon activation, there is nothing manual about the process,

Fig. 2

The spreadsheet shows a number of separate sections of information:

• home results - with a full win, draw, lose, for and against, and points analysis
• away results - with a full win, draw, lose, for and against, and points analysis
• overall results - with a full win, draw, lose, for and against, and goal difference analysis
• total points tally

Note again the colour striping in the 'Southampton' row. This is another presentational facility for clarity, to highlight the statistics for the selected team.

Maintenance

To support configurability and provide future value, the workbook provides a number of maintenance facilities. These are accessed via a custom menu that is added to the standard Tools menu.

The maintenance functions provided are:

• New Season - prepares the workbok for a new season by nominating upto 5 teams for relegation, with their replacements, and then clears out all of the current results
• Refresh Results - clear out all of the existing results
• Add a Team - add another nominated team to the table
• Rename a Team - change the name of an existing team
• Remove a Team - delete a team from the table
• Set Points - the workbook assumes 3 points for both home and away wins, 1 point for a draw. This can be tailored to other points scores

A Non-VBA Solution

The solution discussed above uses VBA to update the tables, to provide the presentational touches (highlighting etc.), and the congurability. In addition, in the download, a non-VBA solution is given in the workbook 'xld.league_table_sans_vba.xls'.

The non-VBA solution uses the same principle, the same results entry, the same league table, as the version with VBA. Because there is no VBA, the highlighting and the configurability is not supported. The table updating is still automatic, it is just not delivered by VBA, but by worksheet formulae.

The non-VBA version allows the season and the scoring system to be changed, this data is stored on the 'Data' worksheet.

To add, remove or change teams, it is necessary to change the data in column A and row 2. If adding teams, be sure to insert a new row after the last exsiting row, and a new column after the last existing column, so as to ensure formula integrity. Then sort just that part of the worksheet.

To refresh the results at the season start is, unfortunately, a largely manual operation. The worksheet is protected (not password protected) as it is imperative that the contents of the cells where the away team and home team intersects is not changed.

In Summary

There we have it, a self-contained application to provide league tables. And with its maintenance functions, it can be configured to your specific needs.

If you have any suggestions for enhancements, improvements, or have found any bugs in the workbok, mail the details to us.

Download

Click here to download two example workbooks (104Kb) that cover the VBA and non-VBA solutions.