I created a Google Sheets to track the status of our unit’s medical form (680-001_ABC), Youth Protection Training (YPT), and Photo Release. This Google Sheet reflects the status of our forms and will color code various cells based on validity. It is a work in progress so feel free to suggest improvements.
Note that this form tracks the status of those items but does not contain the content of those items. Per BSA policy, the medical form and anything containing PHI is not digitized. Our troop stores these forms in a bright red binder in a locked cabinet and only the Scoutmaster, Committee Chair, and Outdoor Activities Coordinator accesses the binder. For trips, the trip coordinator takes a paper copy with them on the trip.
You can find the tracker here: https://docs.google.com/spreadsheets/d/1PJJVaCpxqIU8E7LSCf1-6rbPrlXjI6zRK4a6XtuDrr4/edit?usp=sharing and you can make your own copy by clicking “File – Make a copy” if you are signed in to Google.
To view a larger screenshot, click here: https://www.johndball.com/wp-content/uploads/2024/03/Screenshot-2024-03-27-090923.png
Columns A & B – these columns are either GREEN or RED based on data from columns E, F, G, H, and I. If the YPT date says “Youth”, then the cell in column I will turn green and will not affect the cells in columns A or B.
Columns E-I – these columns are either WHITE, GREEN, RED, or AMBER based on data hints found on row 3. For example, a form expiring within the next 30 days will turn amber (see lines 11-13). An expired form is red. A valid date will be white and a valid response will be green.
Column F – you may have some scouts or scouters that have a valid 680-001 parts A, B1, and B2 but not a part C (physical). In those cases, your less than 72 hours or more than 72 hours will reflect a GREEN-RED status (see line 8, 14, 15). The inverse is true: you may have a valid part C (physical) but expired or missing part A, B1, and B2. In those cases, both cells in column A and B will reflect a RED-RED status (see line 13).
Column J – this column is for tracking our photo release form. A YES or NO response will turn the column GREEN or LIGHT YELLOW but will not affect the status of columns A or B.
The conditional formatting values
Column A – RED: =OR(E5:E1002<TODAY(),I5:I1002<TODAY(),$E:$E="Not on file",$G:$G="Not on file",$H:$H="Not on file",$I:$I="Not on file")
Column A – GREEN: =OR($G:$G="Yes",$H:$H="Yes",$G:$G="None",$H:$H="Exempt")
Column B – RED: =OR(E5:E1002<TODAY(),F5:F1002<TODAY(),I5:I1002<TODAY(),$E:$E="Not on file",$F:$F="Not on file",$G:$G="Not on file",$H:$H="Not on file",$I:$I="Not on file")
Column B – GREEN: =OR($G:$G="Yes",$H:$H="Yes",$G:$G="None",$H:$H="Exempt")
Column E, F – RED (1) is a “text contains not on file” rule
Column E, F – RED (2) is a “date is before today” rule
Column E, F – AMBER: =TODAY()+30
Column G, H – same as E and F with the addition of two “text contains” rules: “Yes” and “None” or “Yes” and “Exempt (680-451)”
Column I – same as the previous rule with an additional “text contains” the word “Youth”
Column J – text is exactly “yes” or “no”
Again, this is a work in progress so feel free to suggest any improvements. Happy scouting!