ADLG Spreadsheet Hell

Introduction

My love of a good spreadsheet and a pretty data table has become far more widely known than I ever imagined. Recently, it led me into attempting something I don't normally do. It was true test.

I was asked to repair a spreadsheet from a UK competition. It wouldn't pass the online tests required before its inclusion in the Art de la Guerre (ADLG) database. The organisers had cut and pasted data leading to all sorts of errors on testing.

Checking the data for a five game event with 32 players was a task and a half! I also discovered that xls files produced by non-Microsoft programs invariably fail the online tests with multiple errors, suggesting differing file headers as the source of the problem.

There but for the grace of God ...

To repair the sheet I was forced to work out how the official spreadsheet works. I also learnt how easy it is for event organisers to make simple mistakes and then struggle to correct them.

The official sheet is one form, albeit a large one. It has all the strengths and weaknesses of such an approach: simple presentation offset by complex data entry requirements.

I began to wonder if there was an easier way? So, whilst Storm Darragh made everyone miserable, I sat at my computer to see what could be done.

My approach

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 also wanted to replace a complex form with simple tables.

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 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 simple change formed the basis of my alternative version. I then worked through the other features of the original adding them one by one. Turns out there are quite a few and things got more complicated.

Looking back, it was a lot harder than I thought. Partly due to technical reasons, but also because there's a lot involved in running and recording an event.

There's more ...

At first I planned to document all my efforts here. However, once I'd explained my design choices and written a set of instructions, it was far too long for a blog post. So I've split it into three four parts. This is the first. The others are available online:

• Design choices
• Step by step guide
• FAQs

The sheet is awaiting testing so I've not released a public version (yet). However, if you are interested, and you'd like to try it for yourself, get in touch by email.

Technical note

The spreadsheet was created with Google Sheets and copies will be available 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.

Closing remarks

This project was a perfect example of how streamlining things is never straight forward. I may have designed a better mousetrap. Then again I may not.

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

This may be a barrier to adoption; no matter how well presented the data is. The deceptive simplicity of filling in a familiar form, even with its many drawbacks, may still prove the easiest to grasp.

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.

Never mind. Storm Darragh has passed and I can get on with other things.