Book Checkout - Zangle Roster SS
version 1.4 - Aug 20, 2009
a spreadsheet created by Pete Tryon
The purpose of this spreadsheet is to serve as a place to record all of your students and
to keep track of what books are passed out to them. Then upon collection, it allows you
to turn in a list of missing books with pertinent information to your FDCC (stock clerk)
electronically. This allows the FDCC to process the fines in a more timely manner. I
would use it even if my FDCC did not.
Given that I am interested in making this work (and I hope that it is relatively straight-
forward), I am willing to answer questions, either by e-mail or telephone:
tryon_peter@asdk12.org, 742-6317 (Or to show you in person at SAHS)
If you are not able to use it because it is missing some key feature, please let me know. I
might try to implement it.
Feel free to distribute to others.
Broadly speaking:
1) Put all of your students in the spreadsheet with their names and student IDs. If a
student has more than one book assigned, he/she has more than one entry. Data can be
copied and pasted from Zangle.
2) Create and print signature sheets if wanted. These may be used to collect book
numbers from the students at the beginning of the year.
3) Make sure that book numbers are entered.
4) Transfer students can be added from Zangle or manually.
5) When books are collected, check them off and flag those that are missing.
6) When ready, save the missing books in a data file and submit it to the FDCC.
7) This does not notify the FDCC after a student returns a book. You will need to do that
independently.
8) You can also print rosters to paste in a traditional 'Green Gradebook' if you wish.
Macros are used:
This spreadsheet uses Macros and they must be enabled. If your computer does not
allow that, you must change the security setting. Most Macintosh do not need this, most
Windows do.
In Windows, change the Security level to Medium - From the Tools menu, Select Macros
/ Security
Various bits of additional information:
There are three columns of names to the right with first name and last name. These are
available for use if someone wishes to do a mail merge document (letter home, etc.) with
a different format.
If you lose the leading zeros to your student IDs, it is because the cell formatting was
changed. Make sure the format of the ID column is set to Text. A little green triangle
should show in the corner of the cell when a number is in it.
Convention: Green-shaded boxes are places that you should be entering information
New version: If you are converting to a new version of this spreadsheet, you need to
copy and paste the information from the AllStudents worksheet. You can also copy
MissingBooks and Archive if wanted, but they are not necessary. When you copy and
paste, make sure to use Paste Special and select Values only. Otherwise you will copy
the buttons, too.
Caution: the various Clear buttons have no warning messages. When you click them, the
data disappear! (Save early and often!)
VI. How to delete rows of data
By using macros, this spreadsheet does not use any formulas in cells. Consequently,
you can delete rows of information at will. Generally speaking, do not leave blank lines
between rows, although in RawData, it does no harm.
To delete a row, click on the number to the left of the row and then select Edit / Delete
I. Add students to the AllStudents worksheet.
Add a textbook automatically if you wish.
(three possible methods)
1) Copy data from Zangle page and paste into RawData. Start from scratch.
a) Launch Zangle, select Zangle Reports.
b) Expand TeacherConnection, Class Rosters.
c) Click on Class Checklist Rosters. If no classes show up, click on the date and select August 19, or some date
inside the semester.
d) Click Create Report. Depending on your computer and browser, you might see any of several views at the
next window.
e) Highlight all information on all pages (click on button for more instructions)
f) Copy highlighted material (Ctrl or Open-Apple C)
g) Click on cell A1 of the spreadsheet and paste information in (Ctrl or Open-Apple V)
h) Click on the List Periods & Classes button to identify all of the classes.
i) Enter (copy/paste) book information for each class. I would store it in the cells to the right so that I didn't have
to look it up each semester.
j) Click the Add Zangle Data NEW CLASS button and then examine the AllStudents worksheet
2) Copy partial data from Zangle page and paste into RawData. This updates students.
Follow the same steps as above, but use the Add Zangle Data MERGE CLASS button. This will only add
students who were not in the original list. (It determines this by Student ID.) Changing names will not affect it.
They are placed at the bottom. Use the Sort buttons to re-sort your data. It will not change students already in the
list.
3) Use the AddStd worksheet.
Enter data on the AddStd worksheet and click the button. This adds them to the bottom of both the RawData and
AllStudents sheets. It uses the same books listed in the RawData. You will probably want to re-sort the
AllStudents. The student ID is required.
4) You can also use traditional spreadsheet skills to add and modify data within the AllStudents worksheet. Make
sure that you include the student ID number and follow the same format that is given. You might insert lines,
delete lines, use Fill Down. The number in the first column does not matter. But you should not leave blank rows
in the middle of your data.
II. Create signature sheets to pass out to class.
Some teachers use Signature pages that they pass out to the class and have the students enter their book
numbers and sign their names. Feel free to format as you see fit. Note that you need to go back to the
AllStudents worksheet to enter book numbers.
To create them, go to the worksheet and read the instructions.
III. Add a new book to existing classes
(two methods)
1) Automatically: By clicking Add Zangle Data NEW CLASS again, you will have an additional set of your
students added. (Change the book title first, and you should be all set.) If you end up with students listed in
AllStudents that you don't need, delete the lines in the worksheet.
2) Manually: If it's just a few students, you can work directly in the AllStudents worksheet. If you give a second
copy of a book to someone, Copy his data line and Paste it at the bottom. Edit the book number and re-sort.
IV. Record missing books
At your convenience, throughout the year, you may record data as books are turned in (or not).
The AllStudents worksheet is your 'master' copy of your book data.
Make entries in column J whenever you want. Anything that does not begin with 'R' will change the cell to red.
This indicates that when you submit missing books, that record will be submitted. You may clear the cell (or type
something like 'returned') and the student will not be submitted.
When books are returned, you can either mark them as such or just delete the entire row.
To deal with a fine for a damaged book, change the price of the book to the amount of the fine and enter a
comment as to the reason.
V. Submit missing books to the stock clerk
When you are ready to submit missing books to the FDCC (stock clerk), carefully examine the AllStudents
worksheet. There should be a red cell for every book that you are submitting.
Go to the MissingBooks worksheet. You can clear old data from this sheet whenever you want. Any record that
says 'SAVED' will be ignored on processing.
Click the 1) Copy Book Fines button. This will move all records with Red cells to this sheet. Examine them for
accuracy. If you need a 'do-over', clear the sheet, go back to AllStudents and redo the Red cells. This places a
date and 'MissingBooks' in the AllStudents page to show when this was done
Click the 2) Save Book Fines button. This will open a dialog box to choose a file name for saving. The default
name should have the teacher's name and date in it and is recommended. The data need to be saved as a 'text
file' that can be read automatically by the FDCC. You will need to e-mail it or put it in a dropbox for the FDCC.
Mac users need to change the file type to text file (see additional instructions for a screen shot.)
The data will be added to the Archive worksheet. This sheet is not used by the program but serves the teacher
as a record of what has been saved. Mistakes can be deleted from it. The temp worksheet holds precisely what
was saved in the text file.
Give the saved text file to the FDCC, either by e-mail or drop box.
FDCC - to retrieve data (The FDCC worksheet is the only one that you need use)
Warning: this spreadsheet is not meant to be the final resting point for these data. You should copy and paste
the data to a spreadsheet that you use for your fines. The button that reads all the available files will first erase
the FDCC sheet.
Place all of the incoming data files into one folder with nothing else in the folder. Windows users can read all of
the files at once. Macintosh users will need to open each file, one at a time.
Note that names are listed in the right-most columns with first / last format for use in mailmerge letters.
This is the worksheet where you enter data from your Zangle
page. Copy and Paste Special into column A
NEW CLASS adds all of the students at the bottom of
AllStudents.
MERGE CLASS only adds new students who were not there
before (as determined by Student ID #).
Class: H2232-1 - Biology I
Period: 1 Room: F113 Teacher name: Period:
Teacher: Teacher, 44 (1000009)
Student Name Gndr Grd
Bautista, Haiden B (1380685) M 10
Baxter, Mateo B (3508586) M 10
Boyer, Curtis B (9433053) M 9
Eason, Tess E (2004080) F 10
England, Amya E (2550118) F 9
Key, Cindy K (3134153) F 9
Palmer, Jaden P (3301233) M 9
Peoples, Jamie P (7925177) M 9
Rasmussen, Osvaldo R (8786022) M 9
Villegas, Eugene V (1561881) M 9
Webb, Kylie W (6115198) F 9
Total Students: 11
Checklist Roster as of 8/19/2009
Ordered by Student
South High School
Report Date: 8/11/2009 Page: 1
Class: H2232-2 - Biology I
Period: 2 Room: F212
Teacher: Teacher, 44 (1000909)
Student Name Gndr Grd Sample Data - when you copy and paste, it should
Green, Jayden G (4463062) M 9 Class: H2232-1 - Biology I
Kuhn, Kole K (5691056) M 10 Period: 1 Room: F113
Total Students: 2 Teacher: Teacher, 44 (1000909)
Checklist Roster as of 8/19/2009 Student Name Gndr Grd
Ordered by Student Bautista, Haiden B (1380685) M 10
South High School Baxter, Mateo B (3508586) M 10
Report Date: 8/11/2009 Page: 2 Boyer, Curtis B (9433093) M 9
Eason, Tess E (2004780) F 10
England, Amya E (2550118) F 9
Key, Cindy K (3134153) F 9
Palmer, Jaden P (3301233) M 9
Peoples, Jamie P (7925177) M 9
Rasmussen, Osvaldo R (8786022) M 9
Villegas, Eugene V (1561882) M 9
Webb, Kylie W (6115198) F 9
Total Students: 11
Checklist Roster as of 8/19/2009
Ordered by Student
South High School
Report Date: 8/11/2009 Page: 1
Class: H2232-2 - Biology I
Period: 2 Room: F212
Teacher: Teacher, 44 (1000909)
Student Name Gndr Grd
Green, Jayden G (4463062) M 9
Kuhn, Kole K (5691056) M 10
Total Students: 2
Checklist Roster as of 8/19/2009
Ordered by Student
South High School
Report Date: 8/11/2009 Page: 2
sheet where you enter data from your Zangle
d Paste Special into column A
dds all of the students at the bottom of
S only adds new students who were not there
rmined by Student ID #).
Adding the book name and price is optional. You can add
them manually to the AllStudents page instead.
book name
Class ('skip' to not fill period) book price
Forensics for Dummies $39.00
Veni, Vedi, Vici $62.00
Biology $115.00
Healthy Lives $55.23
Innocents Abroad $8.98
Sample Data - when you copy and paste, it should look like this (in column A)
Class: H2232-1 - Biology I
Period: 1 Room: F113
Teacher: Teacher, 44 (1000909)
Student Name Gndr Grd
Bautista, Haiden B (1380685) M 10
Baxter, Mateo B (3508586) M 10
Boyer, Curtis B (9433093) M 9
Eason, Tess E (2004780) F 10
England, Amya E (2550118) F 9
Key, Cindy K (3134153) F 9
Palmer, Jaden P (3301233) M 9
Peoples, Jamie P (7925177) M 9
Rasmussen, Osvaldo R (8786022) M 9
Villegas, Eugene V (1561882) M 9
Webb, Kylie W (6115198) F 9
Total Students: 11
Checklist Roster as of 8/19/2009
Ordered by Student
South High School
Report Date: 8/11/2009 Page: 1
Class: H2232-2 - Biology I
Period: 2 Room: F212
Teacher: Teacher, 44 (1000909)
Student Name Gndr Grd
Green, Jayden G (4463062) M 9
Kuhn, Kole K (5691056) M 10
Total Students: 2
Checklist Roster as of 8/19/2009
Ordered by Student
South High School
Report Date: 8/11/2009 Page: 2
Extra Data: Store book titles and prices
here, then copy and paste them into the
cells at the top. (I'd keep all the book data
that I had stored here…)
book name book price
Forensics for Dummies $39.00
Veni, Vedi, Vici $62.00
Biology $115.00
Healthy Lives $55.23
Innocents Abroad $8.98
Roughing It $14.99
Moby Dick $14.99
You can edit entries here by
typing them in and Edit/Fill
Down.
Enter book numbers in this
sheet by typing.
This sheet contains the master copy of your data. Changes can be made here,
but keep the same format. It is recommended that you add individual students
on the AddStd worksheet.
Grade Name Student ID Period Teacher Class
can edit entries here by Date entry Comments are not used by
g them in and Edit/Fill was moved to the program but may be
n. MissingBooks entered for the teacher's
r book numbers in this You may enter use. They are copied to the
et by typing. To mark a book as missing, place any a date for the FDCC.
abbreviation (such as 'y') in column J return if you To record a smaller amount
The RED color indicates that it has not wish for damages, change the
yet been 'processed' price in column J and add a
You may use anything starting with 'R' comment here. These additional columns of
or 'r' to indicate the book was returned are available for mail merge
Book Number Book Title Date moved to Comment (optional)
Book Price submit fine (non-blank) MissingBooks Last name
These additional columns of names
are available for mail merge
first name first - last
Enter data in these first 5 columns. The Student ID is required.
When done, click the Add Students button. It will only add the students
that do not have 'Added' as their status.
Students will be added to the bottom of the RawData sheet and
AllStudents sheet. You will probably want to Sort the AllStudents by
Name
These 2 columns are filled automatically.
If you have more than one class with the same period number, it adds the
student to the first class. You can edit the student in AllStudents You may delete rows from this worksheet any time th
manually. but do not leave blank rows between your data rows.
Period Last Name First Name Student ID Grade Date Added (auto filled)
2 Alpha Alphonse 7778877 12 08/20/09
4 Beta Betty 3344555 9 08/20/09
4 Gamma Gaston 1234567 12 08/20/09
ns are filled automatically.
e rows from this worksheet any time that you want,
e blank rows between your data rows.
status
Added
period not found
period not found
This button saves the new (no date shown) data in this worksheet
This button moves data from AllStudents to to a text file to be given to the FDCC. (It's the last step in this
this page and puts current date in process.) It is recommended to not make any changes to this
AllStudents worksheet sheet.
Grade Name Student IDPeriod Teacher Class Book Number
You decide when you want to clear this Col K shows Date
ate shown) data in this worksheet page. The Copy button will add data at Saved in text file for
FDCC. (It's the last step in this the bottom of this worksheet. (I'd keep stock clerk (if no
o not make any changes to this data in this sheet to remind me what is date, it has not been
eet. done already.) saved)
Book Title submit fine (non-blank)
Book Price date saved Comment (optional)Last namefirst name
first - last
Page breaks are automatically
added between each of the
periods.
The Print Area is set so that
This page is optional. only columns B : G are
Some teachers like to print printed. You may type things on this page to
out Signature pages for From the View menu, select print, but changes will NOT be You can print this page for your
students to fill in book Page Layout to see how it will permanent. Enter book numbers on the records. Feel free to change the
numbers and sign. print. AllStudents page. column widths to fit your needs.
Explanation: this is never erased automatically. It is not used by the spreadsheet, but is only a record for
Feel free to delete lines as you see fit. If you leave gaps in the list, they will be filled in the next time.
New information is added to it.
The date is filled automatically with the date that the line was saved to the Text File
ARCHIVE Grade Name Student ID Period Teacher Class Book Number
readsheet, but is only a record for the teacher.
y will be filled in the next time.
he Text File
submit Date Saved in text file for stock clerk (if no date, it has not been saved)
Book TitleBook Price fine (non-blank) Comment (optional) Last namefirst name
first - last
This worksheet is only for those who still use the Green
Gradebook. It should give you pages properly
formatted to print, trim carefully, and then paste/tape
into your gradebook. Note that teachers who have
multiple classes during the same period can sort the
data in AllStudents by Period or by Class and have a
page of all classes in one period or all periods of one
class.
FileName
If using a macintosh, you must read each file, one at a time. Windows users can put all of the
files in one folder, then click on any of those files and it will read all of them. See the 'Temp'
worksheet for a list of the files that are read.
Date Student ID Name Grade Book Title Book Number
This page will be erased when you read all files at once. It is These additional columns of
expected that you copy and paste the data to your Fines names are available for mail
spreadsheet. merge
Book PriceTeacher Class
Comment (optional) Period blank blank Last namefirst name first - last
This worksheet has a variety of headers. You shouldn't need to change any, but do so if you
wish :)
Used for various functions:
(don't touch)
Old School Data is sorted by: period
Operating system: pcdos
print area -> a34:j34
Teacher name for filename -> Teacher
The cell to the right is used in 2009_8_11
naming the text file ->
Signatures - line 1 This page is optional. Some teachers like to print out
Signature pages for students to fill in book numbers
and sign.
Signatures - line 2 Grade
Missing Books - line 1 This button moves data from AllStudents to this page
and puts current date in AllStudents worksheet
Archive - line 2 Grade
AllStudents - line 1 This sheet contains the master copy of your data.
Changes can be made here, but keep the same
format. It is recommended that you add individual
AllStudents - line 2 Grade
Missing Books - line 2 Grade
FDCC - line 1 If using a macintosh, you must read each file, one at a
time. Windows users can put all of the files in one
folder, then click on any of those files and it will read
all of them. See the 'Temp' worksheet for a list of the
FDCC - line 2 Date
Temp - line 2 Date
Version history
version 1.1 _ 8/9/09: first version distributed to others
version 1.2 _ 8/11/09: changed to Class Checklist Roster Report as the data source
version 1.3 _ 8/16/09: Formatted output for old school gradebook
version 1.3.2 _ 8/17/09: dealing with minor old school mac issues
version 1.4 _ 8/19/09: skip to ignore a period, fix last name for found students, checks for bad id in adding students
Page breaks are automatically added
between each of the periods.
The Print Area is set so that only
Name Student ID Period Teacher
This button
saves the
new (no
Name Student ID Period Teacher
Name Student ID Period Teacher
Name Student ID Period Teacher
Student ID Name Grade Book Title
Student ID Name Grade Book Title
e
cks for bad id in adding students
You may type things on this page to
print, but changes will NOT be
permanent. Enter book numbers on
Class Book Number
Class Book Number
You can edit entries here by typing
them in and Edit/Fill Down.
Enter book numbers in this sheet by
Class Book Number
Class Book Number
This page will be erased when you
read all files at once. It is expected
that you copy and paste the data to
your Fines spreadsheet.
Book Number Book Price
Book Number Book Price
Book Title Book Price
You decide when you want to clear this
page. The Copy button will add data
at the bottom of this worksheet. (I'd
Book Title Book Price
To mark a book as missing, place
any abbreviation (such as 'y') in
column J
Book Title Book Price
Book Title Book Price
Teacher Comment (optional)
Teacher Comment (optional)
You can print this page for your These columns are not included in the
records. Feel free to change the 'Print Area'. You are welcome to
column widths to fit your needs. reformat or move them to other
Signature submit fine (non-blank)
submit fine (non-blank) Date Saved in text file for stock clerk
Date date, it has not been
(if no entry was moved to saved)
MissingBooks
You may enter a date for the return if
submit fine (non-blank) Date moved to MissingBooks
submit fine (non-blank) date saved
Class Period
Class Period
Date Comment
(optional)
Col K shows Date Saved in text file for
stock clerk (if no date, it has not been
saved)
Comment (optional) Last name first name
Comments are not used by the These additional
program but may be entered for the columns of
teacher's use. They are copied to the names are
Comment (optional) Last name first name
Comment (optional) Last name first name
These additional
columns of
names are
available for mail
blank blank Last name
blank blank Last name
first - last
first - last
first - last
first name first - last
first name first - last
Saving Text File on the Mac:
A few quirks to saving the file on a Mac:
You must change the Format: to "Text (Tab delimited)". If you do not, 'bad things'
will happen.
You should 'Append file extension' to ensure cross-platform compatability for the
FDCC.
My apologies for not being able to figure out the filter for saving as a text file
automatically
What to do during the first week of school:
I. Fill the RawData worksheet with your classes and book names / prices
II. Save the spreadsheet!
III. Fill the AllStudents worksheet by clicking on the CREATE button.
IV. Create and print Signature pages if you wish.
V. Type book numbers into column H of the All Students worksheet and SAVE.
VI. If you use a 'Green Gradebook', go to the OldSchool worksheet and create and print student
rosters. They should fit the spacing of the gradebook 'exactly'. Cut them up and paste them in. (I did
this at the end of the second week - too many changes for the first week.)
Save the spreadsheet and play around! Try things out and see what happens. If you are worried,
make an extra copy of the spreadsheet and experiment! Hopefully it will begin to make sense after you
try it out.
More on Selecting All Students and Copying them
Four possible methods to try:
I. Shift click all pages: Choose the Select Tool (it might have an Arrow icon).
Click directly in front of the word 'Class' on the first page. Scroll to the bottom of
the last page, hold the Shift key down, and click after the grade level of the last
student.
II. Ctrl (Open-Apple) A: Typing this should Select All. It works on some systems,
not on others. Check to see that all pages (class rosters) are highlighted. If only
one class is highlighted, you can still use this, but you will need to do it several
times, once for each class.
III. Right (Ctrl) - click on the Roster and look for Select All. This might work even
where Ctrl A did not.
IV. In Preview on the Macintosh, you might need to click on the Letter A (the Text
tool) before you can click in front of 'Class' and shift click at the bottom of the last
page.
V. Save the report. This saves it as a PDF file. Then open the PDF file in
Acrobat Reader and go through the same possible steps to copy and paste the
rosters.
The end result should be of the same format as the sample roster shown in the
gold section on the Documentation worksheet.
When students are selected, Copy them to the Clipboard by typing Ctrl (Open-
Apple) C.
To Find the Correct Report
Zangle Reports / TeacherConnection / Class Rosters / Class Checklist Rosters