Qualification Tracker Recommendations/Improvments

Hello All,
I’m looking for a template or suggestions for a Qualification “Tracker”. I’m onboard a US Navy ship and we don’t have a decent system to maintain track of personnel that are “qualified” to stand an armed watch. The attached file “CLEAN_MASTER GUN QUAL TRACKER.xlsx” is a PII (Personal Identification Information) cleaned of what is currently used. The file named “C_VILLE MASTER GUN QUAL TRACKER (V0.91)PIVOT_TBL.xlsm” is my first attempt using a template provided by Trevor Easton from onlinepclearning.com from Australia. I tried adding the Pivot Table to achieve a “Spread Sheet” view for filtering, printing and viewing. I believe a Pivot Table is good for that but would like to filter data to provide names of personnel coming due on Qualifications based on various factors.

The password for C_VILLE MASTER GUN QUAL TRACKER (V0.91)PIVOT_TBL.xlsm is "Online"

For a little background:
The M16/M4, 9mm, and Shot gun initial qualification (valid for 8 months) requires the completion of:
• PQS (Personnel Qualification Standard) - a process to ensure the member being qualified is aware of safety and general weapon components.
• DD Form 2760
• Firing the weapon at a target various distances from the shooter
• Firing the weapon in a Low Light condition
• Firing the weapon in a Practical Weapons Course (the shooter moves around to fire on targets) which applies to only the 9mm and Shotgun.
A member must complete a sustainment course before the qual expires to extend it 4 additional months than redo the full course of fire to re-qualify.

All of that is very difficult to track manually for over 300 crew members and prioritize to identify who needs to shoot.

Thank you all for your time and assistance. I'm in awe of how helpful everyone is on this site, I've learned and improved my meager skills ().

Jim

The second attachment is on POST #6

I have removed your second file because it contains what appears to be private data. If it is fake data let me know and I will restore it. Otherwise you need to create a version with fake data.

@6StringJazzer, thx for yr attention.
Now I can have a look at this with more peace of mind

@Sorensjp,
are you attached to the idea of using a pivot table?
When I understand you well you need some checklist giving you instant insight in the status of the crews (arms) skills. Not sure a pivot table will do that. But then again I'm not too familiar with pivots, so I may be missing things.
I have quite some experience with checklist showing lots of data, but always end up using tables with conditional formatting (and sometimes smart logic).

I am reattaching your file here. There is a hiccup in the board that is preventing me from editing your original post.

Hi Jim,
here's an example of a dashboard using a table with conditional formatting (and no more than that, yet). Just off-the-shelf Excel functionality.
Used a fresh workbook as I didn't want to mess up yours.
It's a very basic example and I'm not at all sure if it's even somewhat going in the direction you want.
Further explainations are on the sheet QUAL TRACKER.
Have a look and play around.