ADLG TS Step by Step
Introduction
I have written an alternative version of the official ADLG spreadsheet used to record the details of wargaming tournaments. Why I started this project is covered in this post. There’s also a discussion of my design choices and set of FAQs available.
This describes how to use my replacement for the standard tournament spreadsheet from the ADLG website. It attempts four main improvements:
- Make data entry quicker and easier.
- Separate data entry from calculations wherever possible.
- Provide tools and reports that will aid organisers and players alike.
- Provide automated checks and balances.
As the completed spreadsheet will be uploaded to, and merged with, the ADLG database the basic structure of the original ADLG spreadsheet has been retained. Even so, I wanted something that was easier to check and edit, and handled the data away from the main page in the ADLG spreadsheet.
If you are interested in what I came up with, and you'd like to try it for yourself, get in touch.
Contents
Document History
- Created: 12:01, Mon 16 December, 2024
- Updated: 09:08, Tue 04 February, 2025
- Updated: 19:57, Sun 20 April, 2025
- Updated: 11:35, Mon 05 January, 2026
What's The Difference?
Recording tournament scores is a complex process. The amount of data required, and the interconnections between the different types of data, gets very complicated in places. There are essentially two ways of doing this:
- One master form with the user handling a lot of the complexity during data entry.
- Simple data entry forms where the spreadsheet handles most of the complexity, but there has to be more than one form.
The original ADLG spreadsheet uses the first approach. I have chosen the second for the reasons stated above. This is not to say one is better than the other: both have strengths and weaknesses.
How It Works
I've broken down the process of compiling a set of results into stages:
- Pre-event preparation.
- Suggested first round draw.
- Collecting results during the event.
- Suggested draw for the later rounds.
- Finalising the results & displaying user friendly standings.
1 & 3 are the core stages and 2 & 4 are optional. Stage 5 involves a few, seldom used, tidying steps and the display of the final standings is automated.
With this approach, there is no need to enter draw pairings prior to the games. The chosen pairings are entered along with the results after the games. This design choice greatly simplifies matters for the organisers and significantly improves data handling.
The spreadsheet was created with Google Sheets and copies will be available shortly (once testing is complete). It contains one simple macro.
It can't be downloaded and used with Excel as it uses functions unique to Google Docs. It can be used offline with Google Chrome or Microsoft Edge. Ideal for events without free Wi-Fi.
After an event, it's straightforward to download a copy of the final sheet as an OpenDocument (.ods) file and upload it for testing. In my hands this file format has passed the online tests every time.
Read on for detailed explanations and instructions. As always with instructions of this type, they appear both lengthy and intimidating. Most of this is due to the underlying complexity of scoring tournaments. Please bear this in mind with what follows. In practice using the sheet is straight forward.
Step by step
The first page in the spreadsheet is the "Index" with links to all the pages (tabs) dealing with each step.
1. Pre-Event Preparation
This all takes place on the "Input_Players" page. It has tables for the event details and player details. The cells requiring data are coloured.
For players, only their ADLG ID, army number & size need be added. There's a search tool to simplify finding ADLG IDs; no need to search the ADLG web site. Everything else is automated.
Player details can be entered in any order. Therefore the roster can be created in the order in which players register and finalised once they have submitted their army lists.
The page also has tables providing analyses of army popularity and army size for people who like that sort of thing.
1.1 New Players
The spreadsheet also handles new players without an ADLG ID. Temporary player IDs can be created on the "Input_NewPlayers" page. For convenience, there is a link to the page in the footer of the search tool.
With the original ADLG spreadsheet new players were assigned IDs like N101, N102, etc. With this spreadsheet new players are assigned IDs starting from 5000. This avoids the complex logic associated with sorting columns containing both numbers (1145) and text (N103).
The change does not affect the uploading and testing of the final results.
1.2 Dealing With An Odd Number Of Players
There is no need to create a ghost player ID to handle byes as there's already one in the database: ID 99.
Add a player with this ID, together with an appropriate (fake) army and (fake) army size, to the list on the "Input_Players" page.
This "player" can be treated as any other as far as the draws and results are concerned. All games against the fake player should be recorded as a (mutual destruction) draw
2. First Round Draw
There are many ways to make a first round draw. Organisers will most likely make the draw themselves based on their knowledge of the players, theme, rankings and national teams (if appropriate). However, the spreadsheet offers an alternative approach.
To give organisers a head start in devising a draw, the Draw_1_Pairs page provides a simple automated draw with limited editing possibilities. It pairs players by their ADLG ELO ranking so first ranked plays second and so on.
The page has three tables. The leftmost has a list of players, their rankings and their armies. In the middle there's an automatically generated draw. On the far right there' a copy of the middle table which can be edited to resolve club and army clashes.
If, on first use, the ID #2 column in the rightmost table is blank, press Ctrl+Shift+Alt+1 to populate it with the suggested draw from the middle table.
To resolve clashes, only the right hand table should be edited. The pairings requiring user action are coloured and the cells change colour to show if they have been changed.There are checks in place in case you miss a player etc.
If a draw has to be remade (fresh players, withdrawals etc.) or to start again press Ctrl+Shift+Alt+1 at any time to restore the suggested draw.
This approach offers a limited subset of the many possible draw pairings. However, you can edit the player numbers in the ID #1 & ID #2 columns to produce any draw. It is suggested that you try editing the ID #2 column first to retain as much of the ELO ranked draw as possible.
If you opt for a randomised, or an completely manual, draw then enter the player IDs directly into columns ID #1 & #2 in any order. This will allow you to check for unforeseen clashes and errors.
3. Collecting Results During The Event
The Input_Results page records the results of all the games. Each result is entered on a row as follows:
| Round | Player #1 | Losses #1 | Result #1 | Player #2 | Losses #2 |
|---|---|---|---|---|---|
| 1 | 486 | 12 | W | 1113 | |
| 1 | 1468 | 14 | D | 1319 | 17 |
| 1 | 1168 | L | 1447 | 14 |
This format should match the paper results slips handed in by the players. All the organisers need to do is write each player's ID on the results slips before entering the data. All the remaining data required is derived automatically. One note of caution: be sure to enter the round number as well as the player details and losses.
A drawn game with mutual destruction works in exactly the same way as any other drawn game, and you must enter both army sizes as losses to award each player 58 points. You cannot leave them blank as you would for a losing player in a decisive game. If you leave both cells blank, the players will be given fewer (and incorrect) points. This inconsistency is due to the way the original spreadsheet calculates the points.
This will involve a lot less work: there's no need to enter losses for defeats and only the result for Player #1 is required. This eliminates possible errors arising from entering the obverse result in the wrong row or the wrong result in the right cell. This should make life a lot easier for organisers not least by allowing them to quickly double check the data against the paper records should problems arise.
The data table can be filtered using the arrows in the column heads to check each round's results or show just blank cells for adding the next set of results.
3.1 Checking Results At The End Of A Round
The Check_Results page checks all the data entered for each round. Its format is based on the main page of the original ADLG spreadsheet with minor modifications. It's there for reassurance really and for those familiar with the original ADLG spreadsheet.
All the colour coded checks are still there, but if the draw has been made correctly and all results have been recorded correctly there should be no surprises.
By design, the checks require the results of every game in a round to work properly. So this page should only be used between rounds. Checking it mid-round will show a lot of spurious errors; most of which will "disappear" once all results are recorded.
Data on this page must not be edited or sorted: it will break the spreadsheet!
4. Later Round Draws
The Draw_2-5_Manual page looks the same as the page for the first round draw and works in a similar way but with important differences. The main one is that it uses the player's current scores instead of their ADLG ELO ranking. It is designed to work between rounds.
The pairings in the two rightmost tables are based on player's points totals such that first plays second and so on. Both these tables highlight clashes where:
- players have already played one another,
- they are from the same club,
- they are using the same army.
After all the results of each round have been recorded, use Ctrl+Shift+Alt+1 to update the rightmost table with the suggested draw from the middle table.
To resolve clashes, only the right hand table should be edited. The pairings requiring user action are coloured and the cells change colour to show if they have been changed. There are checks in place in case you miss a player etc.
To start again press Ctrl+Shift+Alt+1 at any time to restore the suggested draw.
It is pointless to use this page mid-round when not all games have finished and data is missing. The points totals will be unbalanced and the pairings will be meaningless.
This approach offers a limited subset of the many possible draw pairings. However, you can edit the player numbers in the ID #1 & ID #2 columns to produce any draw. It is suggested that you try editing the ID #2 column first to retain as much of the paired draw as possible.
If you opt for a completely manual draw, enter the player IDs directly into columns ID #1 & #2 in any order. This will allow you to check for unforeseen clashes and errors.
5. Finalising The Results
The Live_Standings page presents a user-friendly view of the current state of play and can be viewed at any time, but is especially useful at the end of the event.
The page is ideal for showing players where they stand in an easy to understand table leaving the more complex pages to the organisers.
It's possible to make this page publicly available (via Google Sheets) so players can check it on their phones throughout the tournament.
After the final round results have been checked (see 3.1 above) all that remains is for the organisers to adjudicate on any outstanding ties and, if necessary, adjust the final totals.
5.1 Tie Breakers
This should only be used at the end of the tournament.
The Check_Ties page lists the player's details, their scores and their opponents scores and a blank column for tie breaks. The list is sorted according to the ADLG standard method (descending scores then opponents scores).
If any ties remain, the affected cells will be highlighted. Entering "1" (without quotes) in the rightmost column will resolve the tie in favour of that player.
5.2 Adjusting Points
The Adjust_Points page provides a list of players' details sorted by their ADLG ID and a column for the organisers to apply, which in effect are penalty points for things like late lists, slow play etc.
I don't expect this to be used very often, but it is provided for consistency with the original ADLG spreadsheet