Help Pages for the Athletics Weekly / Power of 10 Results Collection Spreadsheet

Current Version: 3.0.3 (and 3.0.4 for AW internal use, and 3.0.5 for Run Britain)

User Instructions

Getting Started

The Collection system is a single spreadsheet. It does not require any installation. It does require Excel 2002 (also known as Excel XP or version 10.xxxx.xxxx) or later. Check using the Help à About Excel menu.

When you receive the file by email, DON’T open it from the email. Save the attachment direct from your email to your preferred folder (e.g. your desktop). Always move your master file around in ‘My Computer’, NOT by opening it with Excel and ‘Saving As’.

Some Important Dos and Don’ts

• DON’T open this file as an email attachment and then try to ‘save As’.

• DON’T try to use this file with Excel 2000 or earlier, because it only compiles properly against Excel version 10 (XP or 2002) or later because of some of the code necessary to sort data during validation.

• DON’T use versions of the spreadsheet older than 2.0.1 as the data won’t import the the AW/AD/PoT system.

• Even if you manage to make the sheet tabs visible, DON’T delete any sheets by right-clicking the tab at the bottom of the screen and selecting ‘Delete’. Your data will not import into the AW/AD/PoT system if you do this.

• DON’T paste times in from other spreadsheets. Excel has an annoying habit of knowing better than you what you meant to type in. It can change certain time data as you enter it. This spreadsheet prevents data being automatically altered as it is typed in, but if you paste data which has already been altered in another spreadsheet, the damage won’t be corrected by pasting into this spreadsheet. For the experts: if the times are already in cells which were pre-formatted as text then you may be able to paste data without causing any damage.

• DO use the hyperlinks at the top of the sheets, particularly the ‘Back’ link, which will take you to the front cover.

• DON'T Use the Preferences button after you've started entering data - you will lose everything you've entered. This is because all the data entry cells have to have their validation formulae reset.

• TRY NOT TO paste data from other spreadsheets which have their cells locked - see 'Pasting Data' below.

Feedback

Please report any errors as follows:

• Tell us the last steps you took (in as much detail as you can).
• Save the spreadsheet.
• Copy a screenshot of any error message you received (like this and in this order):
  • Open Word and open a blank document
  • Go back to the error message
  • Press “Alt” + “Prt Scr”
  • Go back into Word and paste
• Send the spreadsheet and the Word document with the error message in it to email address: results @ athletics-weekly.com.

Releases

Several enhancement requests are outstanding. We will be looking at, amongst other things:

• A field for relay legs.
• Age groups ending in ‘5’.

Serious bugs will generally be fixed on the Friday of the week in which they are reported.

Before You Start

Macros

Your Excel macro settings must be set as follows:

Take this menu in Excel: Tools à Macro à Security 

This screen appears:

Set you macro security to ‘Medium’ or ‘Low’

• If you select ‘Low’ the program will start without any prompts.
• If you select ‘Medium’ you will be asked to ‘Enable Macros’ every time the program starts. You must then click on the button marked ‘Enable Macros’.
• If you select anything higher than ‘Medium’ the program WILL NOT RUN.

The authors take great care to avoid the introduction of any malicious code.

Terminology

These are the terms this program uses:

• Fixture: An athletics or other meeting happening at one location over one or more days. In short, it’s the thing the organisers organise.

• Event: A race. Each round or heat is a separate ‘event’ in the program’s terms. In short, it’s the thing the timers or judges wait at (or for) the end of.

• Performance: An individual or team’s time and position data in an event. In short, it’s what the athlete does.

Create a New Results Submission

Open the master file (“Results Collection V3.0.3.xls”). Click “Enable Macros” if prompted.

You will be prompted to give the results an Excel file name. Enter a temporary file name and press ‘Save’ - you can use the same temporary file name again later if and when you have used the 'Re-Save' function to automatically assign a name to this workbook.

You will then see the ‘Fixture’ page, which looks like this:

Quick reference:

• ‘Add Event’ adds a new worksheet to the workbook: a track sheet where you can add results for all events, a multi-event track sheet (for pentathlons etc) or a single event sheet for non-track events.
• ‘Commentary’ allows you to add a commentary about the fixture.
• ‘Match’ allows you to add a match result for the fixture.
• ‘Preferences’ lets you edit your name, specify the number of rows in new return sheets or set the option to stop ‘Auto-Complete’ from occurring in Excel while you have this program open.
• ‘Reset’ deletes all data in the current workbook, so you can start again.
• 'Resave' (which only works after validation) will give the file a new auto-generated name based on the entries in the yellow cells above).
• ‘Help’ shows a list of age groups (plus allowed aliases) and event codes.

The 'Online Help' link brings you back to this web page.

Tip:      You should set your full name in correct case using the Preferences button when you first use the spreadsheet.

Tip:      You can move the screen tips, such as the ‘Fixture ID’ tip shown above. Move any one to a preferred location and all messages for this screen for this session will be in that location. This is not something Excel lets we programmers control. It just works like this.

Detailed Instructions:

Your name should be in Cell D5. If it isn’t, press the button marked ‘Preferences’ and enter your name. This will be remembered on your PC. Take a moment to make sure it’s your full name and it’s spelt correctly. It will speed up results processing.

Enter the following data in the white cells:

1. The Fixture ID, which you may know from information sent to you by AW. If you don’t know it, enter 99999.
2. The Fixture Name (as per the AW listings if possible). Please don’t use ALL CAPITALS.

3. A Division name (optional).

4. The Fixture Date (or the first date for multi-day fixtures).

5. The Fixture Location (venue name) or the NUTS track name.

6. Select a fixture type from the drop-down list.

Press the button marked ‘Add Event’. If you have not entered all the above data, you will be prompted to do so. If you have, you will then see:

EITHER this one for non-track events:

• Choose whether you are entering all results or just some of them (partial results).
• If entering all results, choose whether the Validation process will sort the positions for you.
• Pick an event code from the dropdown.
• Enter an event name (try to avoid using the same name as the fixture as this can look like duplicated data in the final output).
• (Optionally) select a default age group. Note that the age groups in this dropdown are genderless – gender cannot be inferred from this code.
• Select a default gender.
• (Optionally) specify how many men and how many women you want printed in AW.
• Press the button marked ‘Add’
• You can enter two or more events of the same code and gender. Second and subsequent sheet names will have a sheet sequence in curly brackets, e.g. ZXC-M-SM{2}.

Tip:      To copy the same data as you entered last time, press the button marked ‘Copy Last Event’.

OR this screen for track events:

You can then select Track or Multi-event, e.g. Pentathlon).

This example shows the Track option:

• Choose whether the sheet is to be for a single day fixture or a multi-day fixture. For multi-day fixtures you must enter a number of days.
• Press the button marked ‘Add’.

This example shows the Multi-event option:

• Choose one of the standard multi-event types or choose ‘Other’ and specify a number of events.
• Pick an event code from the dropdown.
• Select a default age group. Note that the age groups in this dropdown are genderless – gender cannot be inferred from this code.
• Select a default gender.
• Press the button marked ‘Add’.

Tip:      To copy the same data as you entered last time, press the button marked ‘Copy Last Event’.

Enter Performance Data

You will see the return sheet you have just added, which will look like:

NON-TRACK You will see this screen for non-track events:

Quick reference:

• Screen tips are provided on columns D to J.
• ‘Team’ brings up a small screen where you can enter a team result.
• ‘Edit Event’ brings back the ‘Add Event Screen’ for you to change any details you got wrong.
• ‘Check/Sort’ checks you haven’t missed anything out – see ‘Validation Rules’ below.
• ‘Delete Sheet’ deletes this sheet and all references to it on the ‘Fixture’ page.
• The ‘Back’ hyperlink which is added to cell I1 takes you back to the ‘Fixture’ page.

Detailed Instructions:

You must complete all column A and columns D to I. Columns D to J have help tips and validation rules. Column J is optional. You are advised to read the tips carefully.

• In columns F, G and H, you can enter a dot (.) and then press enter to copy the entry from the row above. This only works in row 4 and above.

Current non-track data rules:

• First name may be blank.
• Surname must be at least 2 characters in length.
• Age group cannot be left blank.
• Gender is optional. It is used as a double-check in mixed events.
• Position can include “=” or “eq” but no other non-numeric characters.

Team Results:

Press the button marked ‘Team’ to bring up this screen:

You can enter three team results:

• Overall event team result.
• Men’s team result.
• Women’s team result.

Note: Click the button marked ‘Add’ after each one, otherwise your data won’t be added.

These apply just to this event. Don’t confuse them with the overall match result, which applies to the entire fixture.

OR this screen for track events:

Note: a separate sheet is added for each day of multi-day events with the date in cell H1

Quick reference:

• Screen tips are provided on columns A to N.
• Columns A to C and I have in-cell dropdowns which you must use.
• ‘Validate’ checks you haven’t missed anything out – see ‘Validation Rules’ below.
• ‘Delete Sheet’ deletes this sheet and all references to it on the ‘Fixture’ page.
• The ‘Back’ hyperlink which is added to cell J1 takes you back to the ‘Fixture’ page.

Detailed Instructions:

You must complete all columns up to and including column J. Every column has a help tip and validation rules. You are advised to read the tips carefully. They include one useful feature:

• In columns A to D, H, I and K you can enter a dot (.) and then press enter to copy the entry from the row above. This only works in row 4 and above.

Current Track Data Rules:

• Columns A to C must be completed.
• Column D is to enter race/round code and heat number, e.g. “A”, “B”, “h1”, “h2”, “r1”, “r2”, “s1”, “s2”, etc. Max 5 characters – only characters 1 to 3 can be non-numeric.
• Age groups in column I must include a gender reference, so that U13 must be U13M, etc. Every entry should start or end with ‘M’ or ‘W’ (or end with ‘Boys’ or ‘Girls’).
• On sprints only, you can enter the wind speed against the first performance only.
• First name may be blank.
• Surname must be at least 2 characters in length, except for relays where they may be left blank.
• Sequence data is only for jumps, etc, not multi-events).
• Position can include “=” or “eq” but no other non-numeric characters.

OR this screen for multi-event sheets:

Quick reference: 

• Enter events and dates in the circled area above. The events are a drop-down and the dates are free text but must be valid dates.
• Screen tips are provided on columns D and above.
• Column G has an in-cell dropdowns which you must use.
• ‘Validate’ checks you haven’t missed anything out – see ‘Validation Rules’ below.
• ‘Delete Sheet’ deletes this sheet and all references to it on the ‘Fixture’ page.
• ‘Wind’ adds (or in fact un-hides) a wind column for each event which can have one (according to AD rules).
• The ‘Back’ hyperlink which is added to cell H1 takes you back to the ‘Fixture’ page.

Detailed Instructions:

You must complete the blue event and date cells as circled above.

You should then press ‘Wind’. If you change an event and press ‘Wind’ again, any unnecessary wind data and columns will be removed from view.

You must then complete all columns from D to the end (the number of cells depends on the main event chosen). Every column has a help tip and validation rules. You are advised to read the tips carefully.

• In columns F and G you can enter a dot (.) and then press enter to copy the entry from the row above. This only works in row 5 and above.

Tip:      You can paste data to these sheets – bearing in mind the warning about cell formatting in the spreadsheet you paste from, but do allow for the hidden column after each event column when preparing data to paste. Make sure columns J, L, etc in the source sheet are present and blank.

Current Track Data Rules:

• Columns D and above must be completed.
• Age groups in column G must include a gender reference, so that U13 must be U13M, etc. Every entry should start or end with ‘M’ or ‘W’ (or end with ‘Boys’ or ‘Girls’).
• First name may be blank.
• Surname must be at least 2 characters in length.
• Points must be entered and must be numeric.
• Position can include “=” or “eq” but no other non-numeric characters.

There is no validation on performances under each event.

Non-numeric times

The following non-numeric times are allowed by the system:

• "DNS"
• "DNF"
• "NTT"
• "TBC"
• "NH"
• "NM"
• "DQ"

Pasting data

The spreadsheet has certain cells which are locked, for a variety of reasons. Because of this, please try to follow these rules:

1. Only paste data into the cells which the system lets you type into.

When copying cells from another spreadsheet it is therefore necessary to:

1. get the columns in the right order in the source spreadsheet;
2. only copy the correct number of columns (and only as many rows as you have set in your results spreadsheet).

2. Try very hard to unlock all the cells you are copying from, as shown below. This is not always important, but if you can avoid pasting from locked cells, then that is good practice. If you need to go back and alter anything after you've validated the sheet, you may find the cells are locked, as the system locks all cells which are set as 'locked', and in pasting the cells you have copied formatting, so locked them.

Format -> Cells (in the source spreadsheet):

Ensure the 'Locked' ticks are removed, so that when the system protects the sheet it doesn't lock the cells.

3. It is best practice to 'paste values' only. That way the formatting of the results spreadsheet is preserved and the locked status of the source cells is not copied over. It is possible to add a 'paste values' button to your Excel menu bar.

Commentary and Match results

On the Fixture page:

Press the button marked ‘Commentary’ to add a commentary about the fixture – this will appear before the results in AW. On the Commentary page, click ‘Add/Edit’ to add or edit the commentary.

Press the button marked ‘Match’ to add an overall match result. There is no need to start it with the word ‘MATCH’. This will be added by the import program at AW. On the Match page, click ‘Add/Edit’ to add or edit the match result.

Commentary and match entry both use a similar screen to the team results:

Click ‘Save’ to write the new or amended data to the spreadsheet.

Click ‘Done’ on either page when you’ve closed this screen.

Completion of the Results Submission

Performance Data

On each sheet, when you have finished entering performances, press the button marked ‘Validate’. This will check that you have entered everything you need to. Please read any messages carefully at this stage. It will also mark the current sheet as ‘validated’.

If you make a mess of the sheet, you can delete it by pressing the button marked ‘Delete Sheet’. This can’t be undone, so take care!

Clicking the ‘Back’ link will take you back to the ‘Fixture’ page.

Completion of Spreadsheet

When you have completed and validated all sheets in the workbook, return to the ‘Fixture’ page.

Cell D19 should show “Yes” against the heading ‘Validated ‘.

If D19 is “No” then

• Use the hyperlinks in column A to go back to each sheet in turn and validate them.

If D19 is “Yes” then

1. Save the results workbook.

2. Send the results workbook to email address: results @ athletics-weekly.com.

Validation Rules

The following validation rules are applied:

Non-track:

A check is made that you have entered at least one line of data.

The following detailed checks are made:

• Surnames are checked to ensure none are blank.
• Club names are checked to ensure none are blank.
• Age groups are checked to ensure none are blank if there is no default.
• A check is made that gender can be derived from the age group.
• Positions are checked for characters which will upset processing:
  • blanks
  • dashes
  • non-numeric data
• Times are checked, to avoid:
  • non-numeric data
  • more than one decimal point

If any test fails the process stops and you are warned what is wrong. The first incorrect cell is highlighted in red.

All times are converted to seconds and stored in hidden cells.

If we are sorting data then:

1. The data is sorted alphabetically by age group then numerically by time
2. The positions in age group are entered automatically
3. The data is sorted alphabetically by gender then numerically by time
4. The positions in gender are entered automatically
5. The data is sorted numerically by time
6. The positions overall are entered automatically

Track:

A check is made that you have entered at least one line of data.

The following detailed checks are made:

• Surnames are checked to ensure none are blank, except for relay events.
• Club names are checked to ensure none are blank.
• Age groups are checked to ensure none are blank and all are in the drop-down list.
• Event codes are all present.
• Default genders are all present.
• Default age group codes are all present and in the drop-down list.
• Default age groups and default genders are consistent.
• Positions are checked (blanks are not allowed).
• Times are checked, to avoid:
  • non-numeric data
  • more than one decimal point
• Veteran and Junior age groups are checked for legality.
• Athlete gender is checked against the event default gender and the event code.

You are also warned if any age group derived from the event codes are different from the event default, but this does not stop processing.

If any test fails the process stops and you are warned what is wrong. The first incorrect cell is highlighted in red.

All times are converted to seconds and stored in hidden cells (for use in processing).

Multi-event:

A check is made that you have entered at least one line of data.

A check is made that you have entered event codes and dates.

The following detailed checks are made:

• Surnames are checked to ensure none are blank.
• Club names are checked to ensure none are blank.
• Age groups are checked to ensure none are blank if there is no default.
• Points totals are checked, to avoid:
  • Blanks
  • non-numeric data

If any test fails the process stops and you are warned what is wrong. The first incorrect cell is highlighted in red.

In all cases

The print area is set.

The validation of this sheet is recorded on the hidden summary.

The overall validation progress is checked.

For Your Information

The button marked ‘Preferences’ on the ‘Fixture’ page will not only reset your name. It will reset the number of rows which appear on a new return sheet.

The location where you save your files will default to ‘My Documents’.

The button marked ‘Help’ on the ‘Fixture’ page will display a list of (genderless) event age groups, (gender-specific) performance age groups, track, multi-event and non-track event codes.

If you make a complete hash of things, you can press the button marked ‘Reset’ on the ‘Fixture’ page. This will completely clear all data from the file.

If you like this program and want something similar for your own business, contact the authors by emailing  sales@suddensource.com.

Results Collection help page last updated on 22/03/10
Copyright © 2010 Suddensource Ltd. All rights reserved.
Information on this site is subject to change without notice.