Editable Primary Roster Excel File - Free Download

You may have noticed that I dropped from the blogosphere late last summer.  It is no coincidence that that is when I was called to be our ward's Primary secretary.  That, on top of everything else, left me with very little time - or energy - to blog.

I was not required to serve in this position very long, only a few months as opposed to the few years one usually holds that calling.  So now, after short sabbatical to recover from the time and energy required of that, I am back to blogging!

To kick it off I am sharing the thing that kept me sane during my time as a Primary secretary: my Primary Roster Excel file.  Anyone who has had the task of tracking 100+ Primary children knows organization is key.    This file will keep your Primary more organized than you ever dreamed (at least it did for me!)

When I was handed the responsibility I was given lots of paper files, each one to track the same children in a different way or for a different purpose (ie by class for one, by birthday for another, etc).  It was difficult for me to wrap my head around everything that way and so I created a spreadsheet where I could track each child on one screen by name, family, birth date, age, class, and date of advancement if applicable.

**To download the editable Excel file click HERE 
(this will take you to 4shared, a free file sharing website).**
*6 Jan 2012 UPDATE: The previous link had been inactivated.  As of this date this link is current.  Please contact me at kristalsblog at yahoo dot com if you have any problems with this link*

UPDATE: As of Oct 20, 2012 this blog has moved to kristalcoles.com  I can be reached with questions at
CONTACT at KRISTALCOLES dot COM

When I created this file I had no intention of sharing it, only to help myself get a hold of a daunting task.  So, of course, it was made to my needs at the time.  You can add to or delete however will best help you.  Below  you will find explanations for how things are set up and the formulas used.

The Key

At the top of the spreadsheet you'll notice the key. Currently it shows "P = Prayer   S = Scripture   T = Talk   LA = Less Active   * = No longer in Sharing Time   º = Official records in another ward"

Prayer, scripture, and talk columns keep track of not only who has given them, but who has been asked to.

"No longer in Sharing Time" - After a child turned 12 I would place the asterisk after their name in the 'Name' field (see J6) to indicate that they still needed to be on the class rolls, but would no longer receive assignments for Sharing time.

"Official records in another ward" - We had enough children of divorce or attending with grandparents on a regular basis that I decided to include this.  The degree symbol (made by holding done the Alt key, typing 167
on the keypad, then releasing the Alt key) was also placed after the child's name.  If there were other notes I would include them in parenthesis (see J8).  The purpose of this was to include children in assignments as part of our ward family even though their names were not able to be counted because their records were elsewhere.  This could also apply to non-members that attend regularly.

The "Advance" Column

This column includes the formula that calculates the exact date a child will advance.

The formula for determining when a child will turn 12 and advance to Young Men or Young Women is:

=DATE(YEAR(C6)+12,MONTH(C6),DAY(C6))

with C6 being the field where their birth date has been entered.

The formula for determining when a child when turn 8 and be eligible for baptism and participation in the Cub Scout and Activity Day programs is:

=DATE(YEAR(C6)+8,MONTH(C6),DAY(C6))

with C6 being the field where their birth date has been entered.

The formula for determining when a child when turns 18 months and can be in nursery is:

=DATE(YEAR(C143)+1,MONTH(C143)+6,DAY(C143))

with C143 being the field where their birth date has been entered.

The "Class" Column

This is one of those things I did for my benefit.  There's probably a better to do it and if you know what it is you can share in the comments.

To be able to sort by class I added a ghost letter before the name of each class.  "A" was added to the oldest class, Valiant 11, "B" to the next class, and so on all the way down to Pre-Nursery (PN).  Simply type "A Val 11"  and highlight ONLY the 'A'.  Change the font color of the 'A' to white.  Done!

*6 Jan 2012 UPDATE:  After some more use of this program I have learned that you could also have ghost numeric system to help sort.  In place of letter prefixes use three-digit numbers, starting with 001.  I would recommend using increments of 5 or 10 (ie 001, 005, 010, 015, etc) to leave yourself room if you need to add a class later.  This way you will not need to rename all the subsequent classes to keep them ordered.  For example: [A Val 11] and [B Val 10] would now be [001 Val 11] and [005 Val 10].  If you needed to add another Valiant 11 class it would look like [001 Val 11A], [003 Val 11B], [005 Val 10].*

The "Birthday" Column

Not to be confused with the "Bday" column, this is where you'll need the child's birth date in MM/DD/YYYY formate for the formulas to work.

The "Age 1/1" Column

This column shows what the child was/will be as of January 1st of a given year to determine class placement.  For this example the year is 2011 and the formula for this field is:

=DATEDIF(C6,"1/1/2011","y")

with C6 being the field where their birth date has been entered.

The "Age Now" Column

This column shows the child's age in years and months.  It is automatically updated whenever the file is opened.  It is somewhat superfluous since you have the date of advancement but it helped me to be able to see the ages.

The formula for determining a child's age in years and months as of TODAY is:

=DATEDIF(C6,TODAY(),"y")&" y "&DATEDIF(C6,TODAY(),"ym")&" m" 

with C6 being the field where their birth date has been entered.

The "P", "S", "T", and "LA" Columns

Tracking child assignments and less actives.

When a child was given an assignment and they were able to fulfill it, I entered the # of the month they did it.  For example, in row 8, that child gave the prayer in February, the scripture in March, and the talk in January

If they were asked to do something and weren't there for whatever reason I used a strike system.  A "/" indicated one strike.  An "X" indicated two strikes.  A "☼" (Alt code 15) indicated three strikes.

Three strikes did not mean they were not able to have assignments anymore.  It was a way for me to keep track how how often they were asked, mostly so parents wouldn't be annoyed with excessive repeated requests.

The Less Active column was updated on the last Sunday of each month.  As I went through the rolls I would mark a red "X" next to each child's name who had not attended at least once.  Doing this allowed me to sort so I could quickly give the Primary President a list of those children and also to give the ward clerk the report of active children (row # of last child listed in youngest Primary class, not nursery, - row # of last LA child listed = number of children aged 3-11 as of January 1st attending at least once during the given month).

The "Bday" Column

Why a "Bday" column when there's already a birth date listed?  For sorting purposes only.  Again, I'm sure there must be a way to sort the "Birthday" column so it will display in order properly, but I couldn't figure it out.  So my solution was to add this column.  Be sure to include the apostrophe just before the three letter month and Excel will sort the birthdays in order.  This was used to know who had birthdays in a given month and was copy and pasted into our newsletter.

***

The rest should be self-explanatory.  There are tabs at the bottom for Cub Scouts and Activity Days.  I also had a tab for teachers, sharing time rotation, and a substitute list.  Add whatever makes it work for you.  If you have any questions or better solutions you'd like to share, please leave a comment.  I hope this can help other Primary leaders as much as it helped me. 

*6 Jan 2012 UPDATE: The file has three instructional comments in blue (one at the top of the worksheet in each tab.)  These are just to serve as reminders and can be deleted without altering the function of the roster.*