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 step by step guide to using the alternative version and set of FAQs available.

Here I want to describe the difference between my approach and that used in the official spreadsheet. As you will see, it’s not obvious which is best.

Contents

• Form Filing
• Data Entry Layer
• Then It Gets Complicated
• Next Steps
• Closing Remarks
• Contact & Other Information

Document History

• Created: 12:01, Mon 16 December, 2024
• Updated: 16:53, Mon 27 January, 2025
• Updated: 11:41, Mon 05 January, 2026

Form Filling

The official spreadsheet is basically one page (or tab) using a form filling metaphor. The user is required to put the right data in the right place.

Namely: complete a roster of players before the event, then enter the results of each game during the event, before making any post-event modifications. So far, so straight forward.

The data entry table is large: 41 rows deep by 51 columns wide. The number of visible columns is reduced during use by hiding some, and by macros (in some versions).

Many columns use formulae to calculate points and reduce the amount of data entry by bringing data forward from the roster section. There are various checks in place to validate the data entered and colour code errors. There are other columns for things like breaking ties and issuing penalty points.

The sheet relies on the user knowing what they are doing both during data entry and when correcting errors. For example: each game requires the user to enter six pieces of data in two sets of three. The first set is for player one:

• player two’s ID number,
• result (W/L/D),
• units losses.

The second set is for player two:

• player one’s ID number,
• result (L/W/D),
• units losses.

This means that the player numbers must be paired (and match the draw) as must the results. Note that, of the six pieces of data required, only five are independent variables; for every win there must be a loss and likewise for draws.

This is an inherently error prone process not least because to record one result a user must:

• find the row of player one in the roster on the far left,
• move along the row to the columns for that round,
• enter the first data set correctly,
• then find the row for player two amongst the other 30-40 rows,
• move along the row to the columns for that round,
• enter the second data set correctly , ensuring the result is the reversed.

Errors usually result in a colour coded warning and the user is left to work out what’s gone wrong before correcting their mistake. It’s fair to say this requires care and attention to detail.

If a result is queried by a player, the above has to be repeated to check the result against the paper slips handed in by the players.

Correcting errors by cutting and pasting data doesn’t always work because it also affects important formulae and the error colour coding rules. It is better to delete and re-enter the data.

This illustrates the strengths and weaknesses of the form based metaphor. The strength is its apparent simplicity but this is offset by complex data entry requirements.

Data Entry Layer

In developing an alternative version I wanted data entry to be as simple as possible and for it to be easy to check for errors. I wanted to move away from complex forms to simple tables. However, I couldn’t just re-write the whole spreadsheet from scratch: the format of the official sheet had to be retained so the data upload works.

Instead, I created a data layer consisting of two pages (tabs) to sit "atop" the main page from the original spreadsheet. One to create the roster and one to enter results:

• The roster is built from just three pieces of data: Player ADLG ID, Amy number and Army size. Everything else is automated using static data tables. The new spreadsheet now contains a list of player IDs gleaned from the main ADLG web site.
• The results of each game are now entered on one row: Player #1, Losses #1, Result #1, Player #2, Losses #2. The sixth piece of data, Result #2, is created automatically as the obverse of Result #1 (see above).

The format of the latter should match the paper results slips handed in by the players. All the organisers need do is write each player’s ID on the slips before entering the data.

This approach eliminates errors arising from entering the right result in the wrong cell or the wrong result in the right cell (see this more humourous example). This makes life a lot easier for organisers not least by allowing them to quickly double check the data against the paper records should problems arise.

Behind the scenes the spreadsheet processes the data, and sorts it so that it matches the row order of the original spreadsheet. This was not incredibly difficult, just time consuming. Links then carry the data forward to a version of the main page from the original spreadsheet. This means the logic and colour coded checks users expect are still in place. I reused this page (tab) to calculate the points for each game. I focused on other parts of the process.

There’s one drawback with this approach. The data in the original table, and all the data fed into it, has to be sorted by player ID so the data sources match. Therefore, it’s not possible to sort the table (to show current standings, ties etc) without breaking the links between the data entry layer and the original page.

To deal with this I created a layer of views containing the results sorted dynamically by descending points etc. One is to show players where they stand, the other is hidden and positioned so it is the page (tab) checked by the online tool.

Then It Gets Complicated

The previous section shows the strengths of the data entry layer. Its weakness became apparent when I tried to replicate all the other functions of the original spreadsheet. In addition to data entry the original sheet also:

• checks the first and subsequent draws for various clashes.
• allows the umpires to resolve ties at the end of the event.
• allows the umpires to adjust player’s scores (penalty points).

It does the first by colour coded checks for each round when the draw is entered and requiring data entry in the "umpire zone" columns (at the far right of the page) for the latter pair.

With the new version the base table of results isn’t ideally structured to handle ties so I created a table presenting the correctly sorted data alongside a column to enter a tie break value. I did something similar for points adjustment. I could have added the latter to the roster page, but as it’s seldom used a separate page keeps the roster page more focused.

To assist organisers, I wrote pages which suggest possible draws for the first (ELO based) & subsequent (points based) draws. Both with all sorts of helpful colour prompts. In practice these pages may see little use: from what I’ve been told many draws are created by hand.

This is a "one page, one task" approach, but to replicate all the features of the original, a simple three page (tab) spreadsheet blossomed to one with eight visible pages. In fact the final total was ten! I added an index page and one that creates temporary IDs for unregistered players.

Next Steps

At this stage, I have chosen not to hide the original table embedded in the new version. However, it doesn’t really need to be visible. I thought a familiar view of the data may help people the first few times they use the spreadsheet.

I would like to believe that, because data entry is now so simple, errors would be few and far between, and any that do will be easily found and fixed. If this proves to be the case I will eventually hide this sheet.

The new version greatly simplifies data entry, and offers ease of editing, but in return the user must handle far more pages than before.

In reality, I’ve created the data skeleton of a dedicated application. Were I to write one, all the additional pages (features) would be tucked away in menus and the whole thing would look very neat. Sadly, programming an application is beyond me at the moment.

Contact Information

• Contact form

#adlg #tech