Professional Development checklist spreadsheet

[arajca]

I am developing a spreadsheet containing checklists for all the specialty tracks. It is not quite finished, but I am looking for some help with proofing reading it to make sure everything makes sense. I took the items for each specialty straight from the appropriate pamphlet.

In the final version, alot of information will be linked to and from the front page which will allow someone to get an overview of the member's ratings. Currently, some items are linked, but not all.

If you have suggestions to items not on the checklist, make sure it is in the pamhlet and not a local or wing requirement. The goal is to have every checklist meeting the standards listed in the pamphlet.

The file is an Excel 2002 spreadsheet.

[Chappie]

Checked out the checklist for both Chaplains and MLOs...no problem with either or suggestions.  Great work.

[arajca]

Here is an updated version with all track listed (those that have pamphlets, at least) and linkages set up. The workbook is protected so information can only be entered in the appropriate cells.

Caveats:
1. This is still in the development stage, although it is functional.
2. For completing items on the checklists, enter only month and year.
3. Any suggestions for adding items to the checklists need to reference the appropriate pamphlet.

If you need the password, PM me.

[Pylon]

They look great to me!  Great work!

[davedove]

It looks good, but I don't see a checklist for Recruiting and Retention Officer.

[arajca]

Whoops. That's why I put it up here for review before releasing it. I'll add that one.

added updated version with R&R track

[Camas]

  Looks great - obviously a lot of work has gone into it - good show!    :clap:

I haven't proof read the whole file.  Some of the AFIADL information under "204" is outdated due to changes involving submitting applications for courses and testing through AU.  I can understand that you're staying with the current publications for your references.  Hopefully a new CAPP 204 and a new CAPR 50-17 will be out soon.  You'll want to stay on top of that so that you can update your chart.

  Again - good job.  Have you thought about submitting your file to "Best Practices" under "Professional Development"?

[arajca]

Once I get it finalized, I plan to submit it.

[arajca]

Here is the final version. I added a drop down list to select the member's grade with the correct appreviations. I also put together a brief instruction sheet, although I don't know if it is really needed.

I'll also be submitting to the best practices section for National.

[Pylon]

Sweet.  Thanks for sharing!

I know I will be passing this along to our unit's Senior Programs officers!  Good luck submitting it to the Best Practices page -- let us know if they actually post it.   

[RogueLeader]

Thanks, this is really helpful.

[arajca]

To all people using these checklists, the file is protected to prevent changes to the checklist items. You can still enter the member data and save it without the password. All the password will do is let you change the checklist items and break the links.

[cnitas]

Hello,

I wanted to say that this is a great spreadsheet!  I am trying to work out a similar spreadsheet for cadet records (if you know of one, please send it my way..it would save me a TON of work).

My 2 cents on how to make it better...You can change the cells on the Member data sheet so that the 'Jan-00' does not show up.  You can do it with an 'if' statement.  For example, you would change cell B27 on the Member data sheet to: 
=IF('200 Personnel'!B32="","",'200 Personnel'!B32) 

This makes the cells blank if nothing is entered on the checklist sheet, but fills in the appropriate date if the information has been entered.

You would need to change each other cells accordingly.

Thanks again for the excellent spreadsheet.

Mark Piersall

[arajca]

Thanks for the code. I didn't quite know how to blank those fields out. I'll add it to the spreadsheet and post teh revised final version here this weekend. I have to work, so the weekend is shot anyway.

As for the cadet side, have you tried SIMS? It handles the cadet proram stuff easily and provides a slew of reports including test logs and CPFT scoresheets. The CPFT scoresheets list what each cadet needs to pass the CPFT, so you know immediately if the cadet passed. SIMS is available on Squadron Command. It uses the information from your downloaded CAPWATCH files so the information matches with National.

[arajca]

Working on Ver 2.

Changed:
Fields linked to dates show empty instead of "Jan-00".

Added:
Member Data: PD Level general task tracking
AEPSM, RSC, & NSC date fields
Primary, Secondary, and Additional Specialty Track drop boxes - up to ten tracks total can be displayed.

A comment on SIMS, it doesn't handle Professional Development tracking very well. It does handle the Cadet Program stuff excellently. I'll probably put the spreadsheet on Squadron Command when I release Ver 2.

[RogueLeader]

I was just creating my officers sheets together, and I noticed that there is no slot for 1st Lt.

[arajca]

No slot where on the spread sheet?

[Al Sayre]

I noticed that the other night when I was messing with it too.  The front page rank selection box doesn't contain 1st Lt.  Just hadn't got around to mentioning it...

[arajca]

Fixed missing grade issue.

[acarlson]

great job! 

how about the OPS-SEC ?   the new requirement in Level 1.

[RogueLeader]

When is there going to be a track for health sevices

[arajca]

When the NLO and NHSO hammer out the legal details.

[arajca]

Another update. Most likely the finished product.

Changes include:
Added OPSEC
Added Staff Sergeant (SSgt) to grade list
Added AF SOS, ACSC, AWC fields
Removing grid lines outside of data fields
Adding the "222 Command" specialty track to the drop down list - used only by current commanders as their primary specialty track.
Hiding the "Standards" worksheet
Added some cell comments and explanations

If anyone has any other suggestions, let me know ASAP. If you've made any changes to it, again let me know so I can see if I want them in the final version - unless they are purely local requirements. All the information and tasks listed are from the CAP pamphlets for each track. I want to keep them that way.

The file is still protected from changes, but data can be entered without a problem. This prevents folks from changing the formulas and breaking links. When you save the file for each member, I recommend saving the file as the member's name. That makes it easy to keep them straight.

When and if a specialty track progression is established for Health Services, I'll add it in. To minimize the impact of having to redo EVERY SM's file, I'll include instructions on how to just copy/paste the HSO stuff and any other changes that may need to be made (including password). That way, the PDO can easily update the affected member's file without having to reenter all the data.

[arajca]

I am using a 19" flat panel display on the computers I use to develop this. If someone is using a smaller screen, please let me know if the "Member Data" sheet shows completely and what size monitor you are using. I am looking at removing the horizontal scroll bar since on my computers it isn't needed, but I want to make sure it isn't needed on computers other members using this spreadsheet before removing it. Idea dropped since it won't fit on a 17" screen.

Additionally, should I put comments on each box on every checklist that is linked to another field or sheet? Already done.

I'm thinking of putting a few additional lines under the "AFIADL" chart for other military PME. The initial value would be "Other DoD PME". Both the "Other DoD PME" and date fields would be fillable. I can fit four lines for this and still maintain spacing. Is it something worthwhile? Already done.

One more thing, I used Office XP to develop this. I know everything works in that version of Excel. If you are using a different version of Excel, please let me know what your results are. Any issues, problems, malfuntioning code, etc. If everything works fine, please tell me that also along with your version of Excel.

[RogueLeader]

My laptop has a 17" Screen, but it does not completely fit.

[SAR-EMT1]

Genius!

[arajca]

Here is the draft instructions for the worksheet. Constructive criticism appreciated. Destructive criticism ignored.

[desert rat]

I like it.

[RogueLeader]

This is very nice.  I'd give it an "Outstanding"

[arajca]

*grumble, grumble, grumble*

Just as I'm about to release this, they go and add another track.

So...the release is on hold until such time as the IT track is released.

[AlphaSigOU]

*grumble, grumble, grumble*

Just as I'm about to release this, they go and add another track.

So...the release is on hold until such time as the IT track is released.

Don'cha hate it when that happens?

[RogueLeader]

No kidding, when are they going to let enough alone?

[arajca]

I'm also waiting for the DDR track to be released.

[Tim Medeiros]

CAPP 228 is released (DDR Specialty Track guide)

[arajca]

Thank you. I'll get it added this weekend.

[Tim Medeiros]

your welcome, I've already sent this to my group PDO an am using to track my own items, looks good and well done.

[Matt]

One other point, instead of read-only-ing it, just protect sheets.  Make sure that you set the cells you want to write in to be unlocked, but that way only those cells that need data can get it.

To protect, simply goto Tools>Protection>Protect Sheet.

If you're looking to make sure that no one can change pages, such as delete a track, same sorta thing: Tools>Protection>Protect Workbook.

Just a couple cents, hope it helps.

[arajca]

I protected the workbook which leads to the Read-only status. The issue is, as you may have noticed, alot of cells are linked back and forth to help reduce some of the tedious data entry. I wouldn't want someone to replace a link with a date then complain the other cells that use that link don't work.

[Tim Medeiros]

IT Track is out

[arajca]

Thanks.

[arajca]

Here it is again, with the IT and DDR tracks added.

I'm not planning any more changes, unless National makes some, or someone comes up with an improvement.

[JC004]

Outstanding.  Thanks for putting all this work in.  You should send it to national for one of the best practices sections.

[SAR-EMT1]

Outstanding.  Thanks for putting all this work in.  You should send it to national for one of the best practices sections.

Second the motion.

[LTC_Gadget]

Ditto, arajca,
Saved for local distro and usage.

Thanxmuch!

V/R,

[arajca]

Got a message from Rob Smith at National:

Congratulations, Andrew! Your submission has been posted to the CAP Best Practices web site. See www.cap.gov/best > Professional Development. Thanks for your leadership in CAP.
Sincerely,
-Rob

ROBERT B SMITH
Deputy Director, Aerospace Education, Cadet Programs & Professional Development

Civil Air Patrol National Headquarters
Toll-free 888.211.1812, Ext. 410
334.953.3922, Ext. 410 (DSN 493)
CAP.gov/best

cc: COWG/CC

[JC004]

Got a message from Rob Smith at National:

Congratulations, Andrew! Your submission has been posted to the CAP Best Practices web site. See www.cap.gov/best > Professional Development. Thanks for your leadership in CAP.
Sincerely,
-Rob

ROBERT B SMITH
Deputy Director, Aerospace Education, Cadet Programs & Professional Development

Civil Air Patrol National Headquarters
Toll-free 888.211.1812, Ext. 410
334.953.3922, Ext. 410 (DSN 493)
CAP.gov/best

cc: COWG/CC

Sweet.  Are they going to send you a cookie?  At least a free branch tape.

[RogueLeader]

Got a message from Rob Smith at National:

Congratulations, Andrew! Your submission has been posted to the CAP Best Practices web site. See www.cap.gov/best > Professional Development. Thanks for your leadership in CAP.
Sincerely,
-Rob

ROBERT B SMITH
Deputy Director, Aerospace Education, Cadet Programs & Professional Development

Civil Air Patrol National Headquarters
Toll-free 888.211.1812, Ext. 410
334.953.3922, Ext. 410 (DSN 493)
CAP.gov/best

cc: COWG/CC

Sweet.  Are they going to send you a cookie?  At least a free branch tape.

Hopefully something. . . .
But not the Black Van

[arajca]

In researching an answer for another discussion, I found a mistake I made in the Operations Officer sheet.

So, here it is with the mistake corrected.

[JC004]

In researching an answer for another discussion, I found a mistake I made in the Operations Officer sheet.

So, here it is with the mistake corrected.

Thanks for maintaining them.  I've updated my set.  I think I'll put these on my wing site.  Make sure NHQ gets the update.

[arajca]

In researching an answer for another discussion, I found a mistake I made in the Operations Officer sheet.

So, here it is with the mistake corrected.

Thanks for maintaining them.  I've updated my set.  I think I'll put these on my wing site.  Make sure NHQ gets the update.

Already sent it to National.

[RogueLeader]

Thanks, I really appreciate it.

[RogueLeader]

Have there been any updates to this?  I thought they updated the HSO, but not sure.

[arajca]

No updates. A HSO track has been proposed, but nothing has been approved.

[sarflyer]

Great spreadsheet!  Thank you, you just made my job a little easier!

[arajca]

Updating again...

changes to Safety Track
adding OE track. Blankity-blank fourth level gonna make the rating summary chart look uneven.

Will post and send to NHQ/PD when finished.

[arajca]

Here is the latest version. Made a few minor typographic changes as well.

I need a couple of folks to review and let me know if I missed anything before I realese it to National.

[RogueLeader]

No section for EO completion, and type under recruit & retention:

Earn on Senior Recruiting ribbon or four Cadet Recruiting ribbons

[arajca]

No section for EO completion, and type under recruit & retention:

Earn on Senior Recruiting ribbon or four Cadet Recruiting ribbons

Is EO a requirement for any level or track?

I felt type of recruiting ribbon was irrelevent, you just needed one or four. As with all of these, the checklist is merely a summary and interpretation. The pamphlet still needs to be used.

[RogueLeader]

True

Not really sure where EO should go, as there is no directive, but as it is required of everybody, regardles of what specialty they are in.  I figured Level 1 would be the best place for it.  As you say, it's only a guide.

Just happened to see the typo, thought I might let you know.

[arajca]

I didn't notice the typo until your last post. Thanks - it's fixed.

[Senior]

I can't get the document to open. I tried to unzip.

[Defender]

Very nicely done.  Thanks for the contribution to the track.

Bringing the program in my own squadron up to date, and things like this help for reference. 

[arajca]

This version is still in DRAFT status. When I finalize it (there are some minor issues I fixed after review but haven't posted here) I'll post it here an send it to national.

Added:
Made some changes and fixed alot of spelling errors. Still in DRAFT form.

Latest version attached...

[CAPSGT]

Not sure why, but when I enter a date into a cell on one sheet, it enters it into the corresponding cell on all of the sheets.  I thought it was odd that by entering a task in a specialty track it autofilled the Level II completion on the same sheet, and when i entered the next task for the tech rating it autofilled the Level III completion.

[tarheel gumby]

 :clap: :clap: :clap:well done this is gold :clap: :clap: :clap:

[arajca]

Not sure why, but when I enter a date into a cell on one sheet, it enters it into the corresponding cell on all of the sheets.  I thought it was odd that by entering a task in a specialty track it autofilled the Level II completion on the same sheet, and when i entered the next task for the tech rating it autofilled the Level III completion.

Please give me some details. What sheet,  what task, what level. Some cells are set to autofill like Level I,II,II etc completion - you enter these on the Member Data sheet and it autofills where needed on every sheet. Name and CAPID are the same way.

[hatentx]

Hey I was looking and something to be right there on the cheat sheet that might help is to have a place to check off when the apprpriote award is given for that level.  This way at a glance we can see qickly and make sure they are good at this level as well rather than running through e services to find it out.

[LittleIronPilot]

Am I missing something...the latest version is asking for a password.

[arajca]

The pasword is only to make changes to the coding. Open as read only, enter the information needed, then save as something else - I use members last name and CAPID (i.e. rajca123456.xls) in a folder that only contains these spread sheets.

[RogueLeader]

DDR sheet does not have a title.  It just says

Professional Developement Checlist

Other than that, outstanding woork.

[arajca]

For some reason, zipping the file introduces all sorts of errors. When I check the working copy, the errors mentioned here are not present.

The only reason I zip it is because I can't upload Excel spreadsheets.

[arajca]

Since the Chaplain track was changed yesterday, I'm holding off on releasing the final update.

[RogueLeader]

Any recent updates?

[SarDragon]

For some reason, zipping the file introduces all sorts of errors. When I check the working copy, the errors mentioned here are not present.

The only reason I zip it is because I can't upload Excel spreadsheets.

Rename it xxx.xls.zip, and see if it will upload that way.

[rmutchler]

I haven't pulled the ZIP file, but is this the same file as http://tinyurl.com/CAPPDChecklist from the national site?

[peter rabbit]

Level V - Direct SLS,CLC, or UCC OR Staff RSC OR NSC    has been changed in CAPR 50-17 to read:

"Conduct a Level I Foundations Course summary conversation and serve in a director or staff member capacity in a CAP approved course (reference Attachment 4). This staffing requirement is in addition to the staffing requirement found at paragraph 6-1."

This means you can also serve as a staff member at SLS, CLC, UCC, or other courses - you don't have to be a director of one of those. However, you can't count what staffing/directing you did for Level IV.

[peter rabbit]

Level I also requires EO and Intro to CAP Safety.