[Home] [Overview] [Pre-registration] [Registration] [Volunteers] [Set-up] [Notes] [Updates] [Tutorials]


An Excel Spreadsheet for Contest Volunteer Coordinators, Version 1

George Norris

Phoenix Aerobatic Club, IAC 69


Overview       The Volunteer Coordinator's job is arguably the most challenging at an aerobatic contest.  Some 22-24 volunteers must be in position around the airport before any competitor can fly and be judged.  Many of the volunteers are also pilots, and can thus only volunteer in certain categories.  Judges and judging school graduates need flights and assists to retain or gain their judging status.  A lot of shuffling of volunteers between categories is required and the VC has to organize and track it all.  Volunteer Log 2002 is designed to be the VC's intelligent assistant: organizing the volunteer data base, keeping track of assignments, identifying problem areas for the VC's attention, and generating the printed assignment information needed by volunteers, chief judges, CD's, and the IAC.  It is designed to operate in conjunction with Contest Ledger 2002,  the companion tool for Contest Registrars. An illustrated guide to the Complete Suite, with input/output of data and printouts is illustrated here.  

Where Are You? What Do You Need to Do?
Before the Contest
  • Download the spreadsheet 
  • Get current IAC judges list in proper computer format
  • Set up the spreadsheet for the contest
  • Practice with previous contest data
After Registration Closes
  • Import volunteer data from the registrar
  • Make initial volunteer assignments
  • Review for problem areas and volunteer requests
  • Print assignments for each category
During the Contest
  • Add new volunteers and adjust assignments for unavailable volunteers
  • Identify pilot volunteers coming from previous or going to following flight categories 
  • Print chief judge clipboard assignment sheets
  • Update judging logs

Before the Awards Ceremony

  •  Provide the CD with a list of all volunteers for recognition

After the Contest

  •  Provide a detailed, accurate log of judges or assistants to IAC 

NOTE: All log functions are available from  the "Volunteer Log" menu, a few have shortcut keys, and many pages have page-specific buttons placed directly on them.  The choice of which method you use to access these functions is up to you ... use whichever you find the fastest or most intuitive.  If in doubt on where to find a given function, use the menu at the top of the Excel window.  There are also many pages (or worksheets in Excel terminology) in the Contest Ledger; select a desired page by clicking on the tabs on the lower left corner of the window.  A second method is to use the "Contest Ledger" ® "Go To Page" menu, which will display all of the pages in a pop-up window.


Before the Contest    

    A copy of the spreadsheet (439KB) is kept here (will it run on my computer?).  Alternatively, a complete Windows installation package with all of the software and documentation is found here (2.9MB).  Right-click to download.  The spreadsheet should be set up on the computer you will use for the contest.  It may be desirable to keep this separate from the Registrar's computer, so that your work will not interfere with the Registrar's.  It may also be desirable to utilize a laptop, if available, for portability.  If the Registrar wants access to your data, simply provide a current copy of your back-up data (described below) and let them run a second copy of the program.  
    Start by un-zipping the spreadsheet and saving it in a disk directory where it will be run.  Print a test page to insure proper functioning.  Using the  "Volunteer Log" ® "Data" ® "Import Judges List" menu on the VolunteerLog worksheet tab, input the current IAC Judge's List from the file judges_list.CSV saved in the same directory.   To develop proficiency, practice with previous contest data saved from the spreadsheet  (contact the author for a backup data file needed). You’re now ready for registration to start.

Registration Day      

    As each pilot presents their completed registration paperwork, the registrar will collect the data the VC will need to staff the contest.

After Registration Closes        

    Loading Data  At the end of registration, the contest ledger contains enough information to create volunteer assignments.  From the Contest Ledger 2002 use the "Contest Ledger" ® "Data"® "Export Volunteer Data" menu or Data Center window (press the "Export Pilot/Volunteer Data" button).  This will create a file, VolList.TXT which will be saved in the same directory as the Ledger spreadsheet.  Copy this file to the computer that will run the Volunteer Log 2002 spreadsheet and save it in the same directory as the log.  Go to the "Volunteer Log" ® "Data" ® "Import Ledger Volunteer Data" menu.  This will read in the data and provide a sorted list of volunteers for contest assignment.  This file can be re-read if the registrar signs up new volunteers and exports a new VolList.TXT file.  Existing entries and assignments in the volunteer log will not be overwritten.  The loading of volunteer data can also be done automatically if a LAN shared folder is available to directly access data saved from the Contest Ledger.  The Contest Ledger saves the data through the "Contest Ledger"® "Data"® "Automate Import/Export" menu and the Volunteer Log uploads it through the "Volunteer Log"® "Data"® "Automate Import/Export" menu.  Thus as registration proceeds, the VC can take a look at the list of volunteers.  This is simplified by looking at the "Volunteer List" worksheet in the spreadsheet, which summarizes the current list of available volunteers.
    If the Contest Ledger provisionally added a judge to the judge's list, it saved an updated copy of the judges_list.CSV file loaded earlier.  For the Volunteer Log to properly recognize the judge when making assignments, you'll have to reload the file from the Contest Ledger directory using the  "Volunteer Log" ® "Data" ® "Import Judges List" menu on the VolunteerLog worksheet tab.

    Making Assignments  Once the volunteer and judge data is read in, you are ready to begin making assignments. Go to the VolunteerLog worksheet tab of the spreadsheet.  This is your working form for making volunteer assignments.  It follows the general form of the IAC Volunteer Log, with spaces for Chief Judge, 5 Grading Judges, Assistants, Recorders, Boundary Judges, Panel Flippers, Runners, Starter, and Unicom Operator.  Each position has entries for each flight: Known (K), Free (F), Unknown (U), and, in the case of Unlimited, the 4-Minute Free (4), each with distinctive background color coding.  At the upper left-hand corner is a Category cell with allows you to select which category you will be assigning volunteers to.  The volunteer entries are limited to the people listed in the volunteer list exported from the Contest Ledger.  

    The best way to enter names is to open the Volunteer List window by pressing the green button on the upper right hand side of the page.  All volunteers are shown in a scrolling table with important information such as:

  • IAC number, 

  • judging status (derived from the IAC Judge List you loaded earlier), 

  • pilot status, 

  • previous experience code (see table below), 

  • volunteer comments (such as how many flights they need to judge), 

  • what categories you have already assigned them to,  

  • priority code for assignment (see table below),

These priority codes are described in the table below and the secondary assignment (++) strategy is discussed in the section below.  Persons already assigned or flying in the current category are not available for assignment and will have a "FALSE" entry in the "Available" field of the table. 

Entries are sorted by: 

  1. Judging Status (current judges, followed by non-judges), and then by 

  2. Flying Category (with non flying volunteers listed after the pilots).  

This makes entering judges or pilot volunteers appropriate for a given category easier.  Refer to the experience and priority codes below to see if a given volunteer is appropriate for a given position:

Experience Code C Z J A M
Meaning Chief Judge Assist Chief Grading Judge Assist Judge -- can read Aresti Willing to work multiple categories
Priority Code + + + + +   (1) + none
Meaning Best for assignment, uses "opposite" category 2nd best, uses either following or preceding category Non-flying volunteers: good availability, but limited numbers All other pilots

(1) the choice of Preceding or Following category is made by radio buttons on the Volunteer Log under "++ Volunteers"

    Entry Method 1:  To enter a name, click in the appropriate position cell in the Volunteer Log, locate an appropriate person in the Volunteer List, and double click the list entry.  If the "Insert Name into All Flights" check box is selected, then the name will be entered into all flights (K, F, U).  This is convenient for initial category assignments.  If the person is not available for this category (flying or already assigned), a pop-up window will warn you and the entry won't be made.  After entry, the selected cell in the Volunteer Log will automatically move to the next available position down (and then up to the top and over one column), so that the user need not leave the Volunteer List window to select the next person.  If the next automatically selected position isn't the desired one, simply click the one you want in the Volunteer Log before continuing.  The entries made in the log are coded to provide sufficient information about the volunteer: <name> - J(<judge status>) - P(<category flying>) [<categories working>].  Thus, "Doe, John - J(Reg) - P(Ad) [US]" is a Regional Judge, Advanced Pilot, and is working the Unlimited and Sportsman flights.  

    Entry Method 2:  It is possible that a person may be entered into one position for one flight and then a second, different position for another flight.   In addition to the Volunteer List window, one can simply right-click the name already entered in the Volunteer Log and then move to the second position and double-click.  The sheet will copy and paste the entry to the second cell and may be faster than method 1..  
    Keep in mind that the entries in the log are NOT where the assignment data is actually stored; a second hidden sheet with all categories and flights stores this data.  The purpose of the Volunteer Log is for entry and display only.  There may be occasional delays as the underlying sheet is updated and the Log is refreshed.  

     Note that the assignment function can be speeded up by using a barcode scanner; see Using Barcodes in Contest Automation.

    To assign volunteers to another category, change the entry in the Category cell on the Volunteer Log ("Volunteer Log" ® "Pick Category"  menu).  The current entries will be checked, the underlying sheet updated, and the new category loaded with any previous assignments.  During the process, it is a good idea to regularly save your work using the "Volunteer Log" ® "Backup Data" menu or "Backup All Data" button to the right of the log sheet.  This may be automated by the "Volunteer Log"® "Data"® "Automate Import/Export" menu, which can regularly save a backup, as well as loading new volunteer data.  You can also save a named backup (or archive) file through the "Volunteer Log"® "Data" ® "Save Archive" menu.  This is a valuable means of saving the current state of the log, which can't be easily overwritten by a later backup.  Use this if you are going to make some experimental changes to the assignments and may want to go back to the original state of the log.  It is also a good way to save your final assignments for future reference.

    Checking Your Work   Once all of the positions are assigned, it is a good idea to double check your work.  Pressing the "Check Assignments" button will ensure that, in each flight, no person is assigned more than twice (as result of cutting and pasting).  Anyone found to be multiply-assigned will be listed in a pop-up window with the offending flight listed in brackets( e.g., "John Doe [K];" for known flight). 

     While it is a good practice to use non-flying volunteers and pilots from the "opposite" category (i.e., the category separated by others before and after the current one) in assigning a given category, this is rarely possible since: a)  non-flying volunteers are limited in number, b) there are relatively few Unlimited pilots to assign, and c) there are normally few Judges among the Sportsman pilots. As a result, assigning pilots from the category immediately before or after the current category often becomes necessary.  This can present issues in getting pilots to and from their volunteer assignments.  By going to the Category Overlaps worksheet tab, and pressing the "Find Critical Volunteers" button, the VC can see these problematic assignments.  This includes safety pilots, who may not be obvious from the volunteer list (listed as "Pilot, Safety [Judge] -> Pilot, Flying").  More information about how critical these overlap are, based on order of flight, is provided by pressing the "Check Against Orders of Flight" button.  This reads the Contest Ledger back-up file (Ledger.BAK or Ledger.CLA files), assuming that they are stored in the same directory as the volunteer list input (VolList.TXT) from the Contest Ledger, and imports the current orders of flight.  The program then computes how critical the volunteer is in regard to either the previous or the next order of flight.  A volunteer that flies first in next category or last in the previous category will have an entry of "1".  A volunteer who flies second in the next category or next to last in the previous, will have a "2".  Entries of 1 or 2 will be highlighted in RED, showing the critical nature of the assignment.  Entries of 3 or 4 will be of concern, but be less critical and will be highlighted in YELLOW.  All others are shown in GREEN.   If you use this feature, be sure that the Ledger back-up file (order of flight)  you are using is current.  If you are not sure, have the registrar back-up their data and, if you are not using the same computer and disk directory, give you a copy.  If on a LAN, using automatic backup ( "Contest Ledger" ® "Data" ® "Automate Import/Export")  from the Contest Ledger on a shared folder will almost guarantee up-to-date data).

    After all categories are initially assigned, looking at the Category Overlaps may reveal that one or two categories have significantly more issues than the others.  This may call for reallocating more of the non-flying volunteers to these categories to alleviate the potential problems.  This is particularly true if one category has a lot of pilots needing to get back to the flight line followed by a category which has a lot of pilots just flown needing to get out to their volunteer assignments.  Such a situation can quickly overwhelm the VC and his drivers.  

Volunteer Allocation Strategy  
One method to minimize the category overlap problem is to follow a prearranged allocation strategy:  if the flight order is U-I-A-S, then Unlimited (U) pilots will work the Advanced (A) category, Intermediate (I) pilots will work the Sportsman (S) category, etc.  If additional positions need filling, then take pilots from either the preceding or following category only.  In this example it makes sense to ADD from the FOLLOWING categories:  add (most numerous) Sportsman pilots to the Advanced category (staffed by the least numerous Unlimited pilots) , add Advanced to the Intermediate, and add Intermediate to Unlimited. Adding Unlimited to Sportsman is probably not possible and usually not needed as there are enough Intermediate pilots to go around.  Then allocate the non-flying volunteers (+) as needed, reducing the worst category overlaps as possible.  The VC's problem is then mainly getting volunteers BACK to the flight line quickly (easier since they're mostly coming from one place, the Judge's line).

Category Flying

Primary Staffing  +++   (Opposite Category)

Secondary Staffing  ++ (Following Category)

Unlimited - Primary

Advanced (marginal)

Intermediate  (some needed)

Sportsman (plenty)

Advanced (not needed)


Unlimited - Primary (not enough)

Sportsman (many needed)


Intermediate (OK, no recorders needed)

Unlimited - Primary (none available)


    Finally, some volunteers may have requested a certain number of assists to qualify for their judge's exam or a certain number of graded flights to maintain judging currency, per the Red Book.  This information should be included in the volunteer experience codes entered from the Volunteer Data window in the Contest Ledger (this is identical to the data entry discussed below under "During the Contest").  If a judge needs 30 flights, then their coded entry in the Volunteer List will include an entry like "J[30]".  If an assistant needs 16 assists, they will have an entry like "A[16]". To see how your assignments satisfy these requests, press the "Make Final Report" button on the Volunteer Log, and then look at the resulting Master Record Sheet, which lists all volunteers and how many flights they have worked in each capacity: Chief Judge (0), Assistant Chief (1), Grading Judge (2), Assistant Grading Judge (3), and all other capacities (4).  Any assistant or judge who has less than their requested number of flights will be highlighted in RED.

During the Contest

     During the contest, assignments will need to be adjusted as volunteer availability changes.  If new volunteers show up, add them to the data base by pressing the "Add Volunteer" button on the VolunteerLog.  A new window, Volunteer Data, will pop up and allow you to enter the necessary information.  Once the name is entered, the volunteer can be looked up in the IAC Judges List by pressing the "Find" button next to the IAC number field.  If a possible match is found, based on the last name, additional information will be given and you will be allowed to accept or reject the match.  Use the check boxes to enter the experience codes (see table above), the comment box to enter free-form data such as when the person is available, and the text box at the bottom to enter how many flights they need to judge or assist for currency.  Similarly, if the original volunteer data must be corrected or modified, right-clicking the name in the VolunteerList will bring up the Volunteer Data window for editing.

    If new volunteers must be assigned or old ones are no longer available, delete the changed entries and make new assignments in the VolunteerLog as described above under "After Registration".  Volunteer assignments must be posted for the all to see (print the VolunteerLog for each category) and an assignment sheet must be included in the Chief Judge clipboard for each flight: print the VolunteerClipboard worksheet after selecting the VolunteerLog category ("Volunteer Log" ® "Pick Category" menu) and VolunteerClipboard flight ("Volunteer Log" ® "Pick Flight" menu).  Ask the Chief Judge to note any last minute personnel changes made on the judging line on the printed Volunteer Clipboard worksheet and return it with the flight score sheets.  You can then retrieve the form from the scorer or registrar after each flight to keep the database up to date.  Refer to, or print out, the CategoryOverlaps sheet for locating and prioritizing transport of volunteers needing to return to or come directly from the flight line.  Printing of these forms is simplified by using the "Volunteer Log" ® "Print" menu, which opens a pop-up window to select what forms and quantities you wish to print.


After the Last Flight       

    The CD Record Sheet  is created by the red "Make Final Report" button (check that the number of flights is correct, as the default setting is equal to the number of pilots in each category) and can then be printed for the CD so that he can recognize and reward all of the volunteers in the contest.

After the Contest is Ended            

    After the contest, the Judging Lists must be prepared for the CD to return to IAC.  The "Make Final Report" button also fills out the required IAC forms (Chief Log, Judges Log(1), Judges Log(2), Judges Log(3)).  Go to the RecordTable worksheet tab, and check that the number of flights actually flown for each category and flight at the top of the page.  The forms can then be printed out ("Volunteer Log" ® "Print" menu) and given to the CD.  This is best done right after the last flight so that the required signatures can be gotten before the Chief Judges leave the contest site.  Finally, make a record of your final assignments using the "Volunteer Log"® "Data" ® "Save Archive" menu.  Thus, if something needs to be changed or reviewed, the current state of the log can be retrieved

For More Information        

    Contact George Norris (480)759-5795, (480)413-3958, or gbnv35b@yahoo.com.


Will it run on my computer? 

The best answer is perhaps ... it requires Microsoft Windows and Office (Excel).  It runs on Office 2000 under Windows 2000.  It DOES NOT work acceptably on Office 97 under Windows 98 and Windows NT 4.0, as that platform does not allow non-modal windows (which the core function of entering names from the Volunteer List window requires).  There will almost certainly be problems with Excel 5.0 or earlier.  I have not tried it with Windows XP and the matching Office suite, but would hope that there is enough backward compatibility to let it function in that environment.  It runs fairly quickly on a 366MHz Pentium laptop under Win 2000.  It runs like molasses on a 166MHz Win NT machine with Office 97.  If you have problems or suggestions contact me at the address above.

Rev 1.82 (GBN 4/12/2003)