Training Tracker for USAF Ancillary Training

Current version is 6.0, released 17 May 2013. For issues or feature requests, contact Maj Dan Riley, 413 FLTS, Hurlburt Field, FL (find me on the global!).

Latest Updates

Version 6.0 fixes the Training Tracker to work with new security measures introduced in ADLS. This version is not as robust as the previous version due to the way everything has to be scripted, but as long as you leave the computer alone after you hit Update Stats (except to enter your PIN if it asks you), it should work ok. If you get an error, check step 7a below!

OK, so here's the details on how to use v6.0!

1. Save the attached Training Tracker v6.txt to your computer and rename it from .txt to .xlsm. The filters through Hurlburt won't let it go through otherwise, so this should get it to the most people at once.

2. On the Setup tab, either click "Migrate Data" to transfer your old settings, or just enter your Organization ID (from ADLS, see previous instructions in the changelog if you forgot). Organization PAS is needed if you are tracking Continuous Learning Points.

3. IMPORTANT!! Click the Enter ADLS Access Code button and enter the Access Code for your UTM access to ADLS. This is the really long and convoluted password you use when you go to "UTM/UDM Administration". This is needed because of the new security measures. It is stored as an encrypted string...not completely secure, but should be plenty secure for most of us.

4. Now go to Stats and run "Update Stats" as normal. Shortly after Internet Explorer is opened, you may have to enter your CAC pin, but that should be the only thing you have to enter. It only pops up on occasion, for no real rhyme or reason, but just pauses the run until you enter it.

5. DO NOT TOUCH ANYTHING! Let it run without using your computer, especially the first time to see if everything works properly. Like I said before, it's much more touchy. I can usually browse my email lightly while it's running, but if you click around too much, you can interrupt the automation.

6. If everything goes well, after the first half finishes (downloading the files), it should run just like it always did. There are a couple of smaller changes you can see in the changelog, mostly adding the new ADLS courses (but those won't stick if you migrate your data!).

7. If you do have an issue here's the troubleshooting steps:

a. If you get a "debug" error that says "file not found" and something like "DOD.xls", go to the setup sheet, and click on the "Fix Save" button. This uses an alternate method of dealing with the files that isn't as ideal, but it seems to be what works for some computers. After clicking that, just go run Update Stats again.

b. If some windows are staying open and either never closing, or the Excel just seems to lockup, play around with the "Timer Modifier" setting, and change the '1' to something bigger. Basically it makes everything take longer, but it may be needed due to speed differences on some computers. You can put "1.2" or "1.5" or "2" or "5" to slow it down a little. If you get as high as 5 or so, going higher probably won't do much good. Conversely, if everything is working smoothly, you can try lowering the value to something less than 1 but greater than 0, ie "0.7" to speed it up a little. It probably won't work though! But you can tinker with it.

c. If you don't put the right Access Code in, you will either get a prompt saying there is none entered, or you will get blank data. Make sure you enter it correctly! For security measures, there's no way for you to check it after you hit "OK".

d. Any other errors, contact me!

Found an error with adding new courses on the Setup sheet. The latest download below has it, but if you have v5.0, use these instructions to update:

Go to "View", "Macros", and just click "Edit". Do a search (ctrl-f) for "InsertCourseForm.Show". On the line ABOVE that, add this:

Sheets("Setup").Select

So that block should look like this:

If Not noAlerts Then
    Sheets("Setup").Select
    InsertCourseForm.Show
    ActiveSheet.Protect
    Application.ScreenUpdating = True
End If

Found an error with supervisory courses. This is updated as of the 2nd v5.0 release. If your supervisors aren't getting the correct courses assigned, please update and migrate your data!

Download Links

Training Tracker.xlsm
Training Tracker Changelog.txt
Training Tracker.xls (same as .xlsm above but may get through IT filters...rename to .xlsm before using!)
Training Tracker.zip (contains .xlsm and the changelog, again may defeat IT filters)

Previous Versions

Training Tracker v5.1.xlsm
Training Tracker v5.0.xlsm

Release Notes and Instructions

(also found in the Changelog above)

---------------------VERSION 6.0 NOTES--------------------------------------------
- Fixed new ADLS security measures
- Added unit's access code and form, with encryption
- One click update for CL points
- Fixed caption of InsertCourseForm
- Replaced Sexual Assault Bystander Intervention with Sexual Assault Prevention and Response Annual training
- Changed DOD-IA to Cyber Awareness

---------------------VERSION 5.1 NOTES--------------------------------------------
- Fixed bug with "Insert New Ancillary Training"
- Fixed supervisor id bug

---------------------VERSION 5.0 NOTES--------------------------------------------

- Added email capability.  Click on either Email button in "Stats" to generate emails to overdue or coming due personnel...also tracks last time emailed next to the button
- With above, added email download from "DOD" tab, into column B on Master Tracker.  Use the Show/Hide Email button to manually edit email addresses
- Added ability to Start tracking CL points, and changed how Stop CL points works to be non-destructive
- Some updates for Office 2010
- Data migration from v4 and newer on Setup tab

---------------------VERSION 4.2 NOTES--------------------------------------------

- Added automatic deletion of personnel if they are no longer assigned to the unit in ADLS
- Changed AEF tracking to 12 months and modified how dates are checked to elimate multiple AEF count problems
- Added current and upcoming AEF to Setup for use in the above fix
- Added "AEF Currency" to Setup sheet to make it easier to change the length of time Tier 2A is good for AEF
- Fixed conditional formatting on AEF highlighting on Setup, Stats, and Slide sheets
- Fixed Insert AEF button on Setup sheet to use the correct macro
- Fixed bug where temp directory might exist but with no files in it
- Fixed rogue yellow conditonal formatting on No FEAR Act
- Fixed error on CL points update if no one is overdue or within 6 months of being overdue

---------------------VERSION 4.1 NOTES--------------------------------------------

- Updated GetCL macro to use PAS code from Setup sheet
- Updated GetFilesSection to allow any combination of Mil, Civ and Contr
- Moved Progress bar update to above other commands to indicate correct progress

---------------------VERSION 4 NOTES----------------------------------------------

How to upgrade:  If you have already setup a previous version and assigned everyone's flights, locations, AEF bands, supervisor status, etc., this procedure will minimize the amount of issues you might have so you don't have to start from scratch!

1. Using your current spreadsheet, run an Update to get all of the latest stats, especially the personnel!  Save this file.

2. Open the new file attached to this email.  Make sure you save it to some location, which can be either on your harddrive, a network drive, or sharepoint.  Just don't run it directly from the email!  Don't forget about changing the extension to .xlsm to get rid of the warning everytime.

3. Go to the Setup sheet and at a minimum make sure your Organization ID is set correctly.  You can add/remove any additional courses at this time, or you can wait till later.  Just make sure DoD Information Assurance is not changed, as this is what's linked to your personnel!

4. Run an Update Stats on this new spreadsheet.  You should get a nearly identical Master Tracker as your previous run, except for the courses for supervisors.

5. Go back to your original spreadsheet, the Master Tracker sheet, and select A4:GXXX, where XXX is your last row of data.  Copy this data (ctrl-c or right click and Copy).

6. Now go back to the new spreadsheet, and select the Master Tracker sheet. Go to the Review tab, and click on "Unprotect Sheet" to allow you to paste the new data.

7. Select cell A4, and paste the data (ctrl-v or right click and Paste).  The Name column shouldn't change at all, and the rest of the user-filled columns (Location, Flight, etc.) will be filled in as before.

8. Finally, run another Update Stats and you should be up and running!

Changes:

- Added a Progress Bar so it doesn't look like it's hanging.  It's a little finicky, so sometimes it will hang for a bit but then catch up, but better than no feedback!

- Added Suicide Awareness (new TFAT CBT pulled out of Human Relations) as a default tracked course

- Offline courses - use a "Course ID" of "Offline" (exactly!) instead of "CXXXXX".  Dates should be tracked on the "Short Name" tab (ie, if you use "TC" as the short name, go to the tab called "TC"), which will automatically update the Master Tracker page.  All of your personnel on the Master Tracker sheet will automatically appear on this sheet, as well as "Rank" (Mil, Civ or Contr) and Base/Location, if you've filled that in.  The only thing you should have to do is enter the date completed on the "Date" column, and if you've entered the correct Frequency in Setup, the due date will be calculated.

-- Notes for this feature:

--- Note that you MUST enter a Date for each individual that requires the training to get the proper statistics.  You will see the first cell "Number Assigned" increase each time someone is assigned a date.  Basically instead of counting the names, it counts the number of dates entered.  You can also enter "Not Attempted" or "Incomplete" instead of a date, just like ADLS.

--- It is probably easier to Insert a New Course and make it Offline initially rather than changing an existing course, as that will give you the full squadron roster automatically.  Otherwise you will only have the personnel listed on that sheet initially.

--- If you don't want to see everyone on the "Short Name" tab, deleting rows directly won't work, because of the formulas involved.  If you want to do some Excel-wizardry to delete the folks you don't want, it can be done, but this method at least automatically adds new people when they pop-up in the database.

--- You can also filter by the Rank/Base to possibly make it easier to find the folks you need.

--- Make sure you only enter information in the Date column, and don't delete the formulas in the other cells!

- Assign course by individual - On the "Setup" sheet, "X" out any courses that you want to assign folks manually, instead of all military, civilian and/or contractor.  Once you set it here, go to the "Assign" sheet, and "X" out (should have a dropdown box) under the column for this course any individuals that you want this course assigned to.  That's it!  Run update as normal, and when you view the Master Tracker, you should only have those individuals with dates, and your Stats should correspond to them properly as well.

-- A couple of notes on this feature:

--- If you want to use this to "assign by AFSC" for example, or assign to large groups of individuals, the easiest way is to make sure folks are organized by Flight on the "Master Tracker" and use the autofilters on the "Assign" sheet.   You can select multiple "flights" in the autofilter, so hopefully your personnel that need "individualized" training will be in similar flights.  It can at least help to narrow down a large personnel list.

--- If you re-order the training on the "Setup" sheet (ie, move DADT below Suicide Awareness, for example), you will probably have to adjust your assignments in the "Assign" sheet, because the column names will change, but the "X"'s will stay in the same spot.  I apologize if this is an inconvenience, but fixing that introduces a couple of other problems that I thought were worse.

--- Normally if you're using this option, don't use any supervisor options. Be careful if you try...if you assign the training to someone, but it's set to "mil supervisor of mil" for example, and they're not assigned as a supervisor, they won't be assigned the training!  In other words, supervisor status takes precedence (or at least should!).

- The AEF/Enabler system has been completely revamped.  Now you can have both enablers and AEF!  And by default you do have both.  If you don't have enablers, you can disable the enabler feature on the 'Setup' sheet by 'X' next to 'Disable Enablers?'  This will hide the Enablers on the 'Stats' and 'Master Tracker' sheets, although the data will still be visible on the individual course sheets.

-- Notes

--- Enablers are considered to be any military members who do not have an AEF assigned.  Therefore, to assign someone as an enabler, simply make sure 'Disable Enablers?' is NOT marked on the 'Setup' sheet, and make sure no AEF is assigned on the 'Master Tracker' to that person.  Now you should see their due date (2 years from the previous accomplishment) instead of Complete or Incomplete.  You will also see number overdue and percent complete on the 'Stats' sheet.  For simplicity/clarity, the 'due in 30/15 days' is not shown, but can be seen on the individual course sheets.

--- The 'Slide' sheet will need to be modified by unit based on what you're looking for, if you use it.  Using the 'Stats' sheet is probably best if you can, but it's a lot of info for one powerpoint slide now!

--- We don't have enablers, so there may be something important I'm missing with this feature, so please let me know if there's something that can make it more useful.

- Not a change, but a reminder, one time courses can be entered by using a "Frequency" of "240".

- Fixed "Frequency" so if you put something other than 12, 24 or 240, it shows "XX months" instead of an error

- Fixed a small problem with adding new training course and the trainee type field wasn't copied

- Changed first row formatting a little on the Master Tracker to make it look a little nicer

- Small modification to "Hide CL Points" for the above formatting

- Made all 'X' on Setup and Assign sheets a data validation drop down to avoid lower case or other incorrect input

- Locked 'Short Name' on the Setup sheet so you can't change it after creating it.

- Changed the way supervisors are trimmed to speed up updates

- Changed the counta to countif on course sheets for new formulas added to template for offline courses

- Increased maximum personnel to ~1000 from ~500

- Fixed DeleteAllPersonnel to really delete everyone!

---------------------VERSION 3 NOTES----------------------------------------------

All,
There were a couple of small problems preventing this from working properly for a couple of organizations.  Use the updated file to allow units up to 500 people and use any file name.  If for some reason you have more people than that, let me know and I can tell you how to adjust for that.  Note that you must save the file somewhere (such as your desktop, or My Documents) and not run directly from the email, or it won't work.

v/r
Capt Riley

---------------------ORIGINAL INSTRUCTIONS (VERSION 2.0)--------------------------

I thought you might want to send this out to the other UTM's on base.  I developed an automated interface to ADLS to track the major ancillary training items that everyone has to keep current on.  This has saved our UTM several hours per week in tracking everyone's currencies.  In addition, it has reduced our overdue rate by a massive amount since now each person in the squadron can easily see when they are due for training events.  Instead of using ADLS's convoluted method or manually inputting everyone's dates into various trackers, this spreadsheet connects to ADLS and updates the entire unit's stats in a few seconds, and gives currency right away.  It also can be linked to a staff meeting slide for the commander to have immediate visibility on the unit's status.  It also tracks Tier 2A training for members in the current and upcoming AEF bands.  Below is a brief explanation on initial setup and use.

The file attached is our squadron's current file so you can see an example of how everything looks when setup properly.  If anyone would like to use this and has any issues, please contact me and I'll try to help out.  Also, if you have any comments/suggestions then let me know!

Note that the attached file is actually an ".xlsm" file (macro-enabled) so you should change the extension or accept Excel's warning.  Also make sure to "enable macros" after opening.

Initial Setup:

If you only want to track the items that are already setup (look at the "Stats" tab and the training table located there), then initial setup is easy.

First, on the "Master Tracker" tab, you'll want to delete all of the personnel currently listed by clicking on "Delete All Personnel" in the upper left.

Next, on the "Stats" tab, change your Organization ID.  This ID can be found by going to any Course Report in ADLS UTM toolset, right-click on "export to excel" and paste the URL to view it fully.  Look for "customerorg_ident=XXXXX" and enter that value.  You may also want to change your AEF bands at the bottom of the page.  If you have more than 5 bands, make sure to "insert rows" inside the box to ensure the formulas pick up the additional bands!

Now run Update Stats on the "Stats" page, wait about a minute for your personnel to get populated with dates.

The final step is to populate the "Master Tracker" with some additional information that ADLS doesn't have.  For each member, add their Status (MIL, CIV, CONTR), AEF Band (if military), and an "X" if the member is a military and/or civilian supervisor.  The location is optional if you have multiple locations in your unit.  These are needed to filter out CBT's that some members may not need.  After you fill out this information, run Update Stats again to get the current information.

If you want to add or remove additional CBT's from ADLS to track, you'll have to do a little editing. The first step will be to edit the macros "GetFiles" and "UpdateStats".  There are comments there that should get you started. You'll also need to add rows to the "Stats" tab and columns to the "Master Tracker" tab.  It does take a little excel knowledge, but you should be able to use a similar existing CBT to get you started.

General Use:

We run this most mornings to have up-to-date stats.  Simply running "Update Stats" is usually all that's needed.  If you have new personnel in your squadron, as soon as their Organization is updated to yours in ADLS, they will automatically show up in the Master Tracker.  This will be most obvious by the fact that you have a new person without "Status", "Location", "AEF Band", or Supervisor status.  Just fill that info in as necessary.  If you have an individual leave, once they change their organization to something different, they will still show up on your Master Tracker, but all of their dates will disappear, indicating that you can delete that row from the spreadsheet (you'll have to "unprotect" the sheet first).

Member Use:

Our spreadsheet is on sharepoint for everyone to access.  This allows them to look at the Master Tracker and easily see when they are due for each training item, and in fact, which items they require!  These are all based on the current Air Force policies.  You may adjust as you see fit.  If a person's name is red, that indicates they are overdue for one of their training items. Individual dates show as red if overdue, blue if within 30 days of being due, and yellow if within 15 days of being due.

There is a tab on the far right called "Slide" that you can link to a powerpoint slide for staff meetings.  All of the text is white for our purposes.

Again, if you have any comments/suggestions/problems, feel free to contact me! I hope this comes as some use for others.