Theory Score Sheet Instructions

Included are both score sheets in Microsoft Excel and converted to .pdf. If you are able to use the Excel worksheets, they will offer you the most flexibility. The .pdf file will produce the same look as the original and can be filled out online, but does not have the ability to perform spreadsheet functions: you will have to alphabetize your students elsewhere. Even if you use a different spreadsheet program you may be able to use the Excel file since most programs can convert files created with similar programs.

Score Sheet Instructions: These are written out below as well, but you will probably want to print them for easy access.

We have sent these Excel Theory Score Sheets in the hope they might be of help to your association. Many years ago, instructions said we could use computer produced forms for our Score Sheets if they looked exactly like TMTA’s sheets. My husband and I worked to build a form in Access that looked like theirs; the data was easy to sort, but the form never looked exactly like TMTA’s. We have since gone the simplistic route of using Excel for our information, and we have come up with a very readable and exact clone Excel Score Sheet.

The Excel link above includes a copy of the Score Sheet which you can use for your association. The spreadsheet has worksheets for each of the 12 grades and one for all of the students. I suggest that you ask your teachers to send you their enrolled students on a form which can be sent as an attachment so that you can cut and paste the information. This will make the entry much easier and quicker.

It is easy to customize this Score Sheet for your association. I will go step by step and number each of the steps to walk you through this process. For some of you, this will be too simplified, but hopefully, those new to Excel will be able to use these forms.

My husband, Greg, is normally is at home and can be reached either by email or phone; he worked at a computer help desk and is very good at training people over the phone. Before we start working on the forms, let’s first look at the Excel program. Go to the Start Menu and find a little green “X” icon; double click to open it. PROBLEM: You may not have Excel on your computer. Call Greg if this is the case and he will give you some suggestions.

The first thing you notice is the menu and icons look a bit like Word. The work area is not a clean white sheet, but rather boxes called “cells”. Please note the columns are labeled A to Z then AA, AB and so on. The rows are labeled 1 to 8000 something. So there is plenty of space to design whatever spreadsheet (work sheet) you want to make. The cells are named using the Column Letter and Row Number. Therefore, the cell in the top left is A1, which is also called the “home cell”. If you ever get “lost” on your spreadsheet, you can go the “home cell” by hitting Ctrl and Home at the same time. Use your mouse to click on another cell to try this. You will notice tabs at the bottom of your work sheet; these will take you to another work sheet. If you move your mouse to the tab and Right Click, you will get a box of options you can use, one of which is to rename the worksheet. The collection of worksheets in one file is called a workbook.

Right under the title bar (very top of the menu), you will see the familiar drop down menus FILE, EDIT, etc… Most of your options are like those in Word. This may be very basic for you, but in File there is an option of FILE/SAVE AS. This will allow you to save your file as a different name or in a different folder or place on your computer. You will use this as you start to use your files on the disk, saving one copy as Theory Fall 2005 and another as Theory Spring 2006 (assuming your association tests twice a year). Another Menu option in Excel is DATA. This allows you to use your worksheet as a data base. All you have to do is highlight the entire worksheet filled with data, and then hit the DATA/SORT option and OK. This will let look at your entries by Grade Level, Student Last Name and then Student First Name. This is the way you enter the students on Test Sheets. When ready to hand out medals, you will sort by Teacher, Last Name, and First Name. Hopefully, you are comfortable in CUT & PASTE, but in Excel, we need to CUT or COPY and then do a PASTE SPECIAL and pick the value option. (This way you won’t mess up the formatting which is already embedded in workbook template). If you make a mistake, you can go back a step by clicking on the icon which shows the rounded arrow to the left (the undo arrow); each click undoes the last action, and you can click it more than once to get back where you want to be.

Make sure you have all of these actions in your head before we get started on making your association’s Theory Test Sheets. A lot of this is attitude; if you are willing, then you can learn. The learning curve on this is a whole lot easier than learning to play how to play the piano or even how to read music. My husband’s stepfather learned to use the PC when he was in his 70’s and used it until his death at 82. Once you see how this works, you will never want to use a typewriter again and will probably move it out to the garage as we have.

Step 1: We now are going to create a folder for keeping all the files we use in Theory. Click on the Start menu at the bottom left of the window and then click on My Documents. Now move your curser to some white space, and RIGHT Click, then click on New. Select folder; a NEW FOLDER will be created with the name of NEW FOLDER highlighted, just type Theory Program or whatever you want to name this file. Inside this folder, you can create other folders for each year you are theory chairman. Now do a FILE/SAVE AS for these instructions. That way you have them on your computer and don’t have to use the CD every time.

Step 2: Open the Score Sheet we have sent to you, either as an attachment on an email or on this CD. Do a FILE/SAVE AS operation on the drop down menu; call it “Score Sheet Fall 05” and do a second FILE/SAVE AS naming the 2^nd Score Sheet Workbook Backup. Note on navigation: on the Save As Menu, it gives you the option of putting the files in a folder of your choice. The menu box has a 6(click on this to see other locations to put your worksheet or use the file icon with arrow pointing up to go up one level). We want to put both copies in the folder we just created called Theory Program.

Step 3: The Excel files you have just copied from the CD are “read only files”. You will have to change the file properties to use them. RIGHT CLICK on the Score Sheet Fall 05 icon, LEFT CLICK on Properties, go down to “Attributes” and remove the check mark on the “read only” option. Now you can go to work. If you received the Excel files by email, you do not have to do this step.

Step 4: Make the workbook YOURS. Spreadsheets are blank with the exception of the All Students tab, which has part of Dallas Southwest MTA. The idea is that you can learn how to sort data without making errors on your data. (We will come back to this a bit later). Depending on the size of your theory program, determine how many sheets you need to format with your Association number, name, and date of test; each sheet has 25 names. You may need only one or two sheets per grade or if you are larger, you may need to add sheets and number beyond 100 students per grade.

A: Just click in the area where you need to add information, like the I.D. Code box with the 2 Xs. To add your I.D. code, highlight the XXs and type in your 2 digit number; do the same with association name (type out your name; don’t use abbreviations such as H, CC, WCH) and the test date. Do this for every grade level for the number of sheets you think you will need; estimate high. Then do a FILE/SAVE which now saves the name and ID number on all your worksheets for Score Sheets Fall 05.

B: Then do a FILE/SAVE AS naming this workbook Score Sheets Spring 06. Open this workbook now and change the dates to the spring date. We will use one workbook for the Fall and one later for the Spring. There is no need to format the 2^nd, 3^rd, and 4^th spreadsheet pages if you will have less than 25 students per grade level. MAKE SURE, HOWEVER, IF YOUR ENROLLMENT IS VERY HIGH TO MAKE THE CHANGES NEEDED ON ANY ADDITIONAL PAGES.

Review: At this point, we should have created a folder called Theory Program in the My Documents File; you should have 3 files in this folder, one for these Instructions, one Score Sheet Fall 05 and one Score Sheet Spring 06. You should have made changes on each spreadsheet needed by adding your Association’s I.D. Number, Name and the Date of the Theory Exam. Any questions so far, call Greg 972-223-5266.

Step 5: At this point, we are ready to start inputting student names into the Score Sheets Fall 05. It will help if you get the entry sheets from your teachers in a manner that will allow you to cut and paste into Excel. You can send a Word form with tables to the teachers that has the same number of columns in sequence. (This form is included on this disk).

Test Level	Last Name	First Name	Grade
1	Brown	Hannah	1
1	Jones	Levi	1

If each teacher uses this format, you will be able to copy and paste to your spreadsheet. Otherwise, you have to type it all in. Note: the test level may not always agree with the school grade. Students can take tests below grade level, but NO medals are awarded to piano students by TMTA. Be sure you know the rules for testing correctly below school grade level and the levels for Vocal/Instrumental students. (See the TMTA –SA Handbook online).

ONE ITEM YOU MUST REMEMBER: EVERY PROBLEM YOU CORRECT BEFORE TESTING WILL INSURE THAT YOUR STUDENTS ARE ELIGIBLE AND CAN BE MATCHED WITH TMTA’S COMPUTER DATA. IT ALSO INSURES YOUR MEDALS CAN BE PROCESSED AS QUICKLY AS POSSIBLE WITH THE LEAST AMOUNT OF HASSLE!!

· Student not enrolled as Student Affiliate. Your association should have a Student Affiliate Chairman. Check with that person before the test to be sure all students are enrolled. There is now a stiff dollar penalty assessed to students and teachers not enrolled before testing.

· Another bug-a-boo which creates problems is name changes from year to year. The office inputs the student data; students must use the SAME NAME from year to year or you must let the TMTA office know the name has changed. We have seen Jimmy now using James, last names now hyphenated by an addition. The computer will only match the same exact name.

· The other major problem is students who change their school grade. Often these are home schooled students; last year, they were in the 5^th grade; by mid- year, they are still in the 5^th grade or maybe now in the 6^th grade. Double check with the teacher to make sure they haven’t either skipped a grade or been held back. I don’t have any real solutions to this problem; if the school grade on the test is different from the one you registered, check it out prior to sending tests off for grading.

We need to get a little work with the worksheet to see how sorting works and you can be comfortable using this wonderful Excel program. Open your Fall 2005 (or the tests you working with) Workbook. I have left some data from my association’s entries from last year to use. Click on Cell A1 and while holding down your left mouse, drag down catty-cornered to I 56; this will highlight (turn gray) the section of the worksheet we want to sort. Go to DATA/MENU and click on SORT. You will get a dialog box with three boxes (first sort, then sort, then sort); in this case, we want to sort first by Teacher, then Student Last Name, and finally by First Name (all ascending with a header row). Now sort by test level, student last name, and then first name. Just play with some of the options. When you are comfortable with SORT, go to the next step.

Step 6: Let’s assemble the entries for the Theory Tests. You should have received email entries from each teacher in the Word attachment entry form shown earlier (copy included). It is the easiest format to copy into Excel. If not, your online or hand-typed registration should include columns with this information:

If you received teacher data in Word, you will have Word and Excel both open at the same time on the bottom tool bar. With the Score Sheets Fall 05 open in Excel, CLICK on the All Students tab at the bottom of the sheet. In Word, go to a teacher’s list and highlight the whole data table excluding the headers. CLICK on COPY, then CLICK on the bottom toolbar for Excel; go to the A2 cell (near the top left corner); CLICK. Then go to the top MENU BAR & CLICK on EDIT, PASTE SPECIAL/VALUE option. This is very important, because it will paste the data and leave the formatting as it should be. You have now pasted this teacher’s students in the All Students worksheet on your Score Sheets Fall 05. (Problems? call Greg 972-223-5266).

Step 7: Now you can enter the Teacher Name. Go to the Teacher column I2; type Last Name, First Name. Do not use the Teacher Number! Now let me teach you a neat little Excel skill. When a cell is active, there is a little plus or cross at the bottom right of the cell. This is a handle; by placing the cursor on this handle and holding down the right mouse button, drag it down to end of the data by that teacher, then release – neat, huh?

REMEMBER OFTEN to SAVE your work, especially if you enter teacher data over several days.

Step 8: Now you need to do a little clean up on the data. Check each record to see if both Test Level and School Grade are the same. The student can take a test lower than his grade, but will not be eligible for a medal. (Hint: if you change the School Grade number to RED, it will highlight so you don’t order a medal later). Remember: there are special rules for Vocalists and Instrumentalists; be sure the teachers are aware of the new testing procedure. These students take the Whitlock tests, but in the school grade column, list “V” or “I” then a dash, then their school grade. (Hint: these could also be in red).

Continue cutting and pasting each teacher’s students the same way. Be sure to put the teacher name in the column, typing it one time at the top, then use the handle to drag it down to the end of his/her student data. Be sure you personally enter all student information from teachers who have hand-written or otherwise not used the computer to send you their entries. SAVE

Step 9: Time to sort the data. Highlight data from A1 to the very last bottom row teacher name. Go to DATA/SORT; in box 1 click on the down arrow for Test Level, then box 2 for Student Last Name and box 3 for Student First Name; Header Row will have a dot in it. CLICK OK. Voila! You are sorted by Test Level and now can order tests.

Place your cursor on the gray row number on the left (not the test level number). Holding the left cursor down (cursor becomes a black arrow è), scroll down to the last row of that test level. Notice the row number counter at this point; that is the number of students in that test level. Write down the number. Go to the next test level row and do the same thing until you have completed all 12 test levels. This is a very useful hint for larger associations! Remember you are allowed only 6 extra tests per level.

Now review the Grade Level and erase if it is the same test level (Most of them will be, only leave the Student’s Grade if it is more the Test Level. (These students will take the test below grade level and will not be eligible for medal)

Step 10: About 4 or 5 days prior to the test date, you need to assemble the Score Sheets. Each test level page can have up to 25 students. If you are a larger association, you have many more than that, i.e., if you have 57 students taking the 4^th grade test, you will have 3 test sheets. To fill out the Score Sheets, open the Score Sheets Fall 05, go to the All Students worksheet and:

Step 11: Once you have given the tests AND the make-up test, go through the printed Score Sheets to cross off students who are no shows, to correct misspellings or school grade levels (monitors could do this at the test site as students finish testing). Now make the corrections in Excel. To delete a name, highlight the whole row except the number (B thru F), then hit DELETE. Go to the last name (B column) of the next student needing to move up, highlight through F holding down the left mouse, and then right click and select CUT. Move to column B of the open row; right click PASTE SPECIAL/

VALUE and names will be added. This is a tricky operation, but keep your wits about you and it saves a lot of typing. Remember that if you make a mistake, there is the Undo arrow to get back to where you were. Double check the new test level Score Sheet to be sure you have a test for each student listed. CLICK FILE/SAVE.

Step 12: Double check the tests one last time to ensure all information is in the heading because if the school grade is missing, 2 points will be lost by the student. Make sure tests are arranged alphabetically by grade.

Step 13: Print out or make copies of each Score Sheet as per instructions on the Cover Sheet. Mail the tests priority if not a local address. Use the free supplies from the USPS. For 50 or fewer tests, the basic 11.625" X 15.125 Tyvek EP-14 works great. Boxes or double envelopes should be used for the larger quantities. Go to www.usps.gov to order supplies and print labels. The advantage of using the printed labels is that they have confirmation numbers on them and are FREE. More information is available about printing these on the enclosed disk or at the website.

Step 14: Tests must be mailed within 7 days of the test, not the make-up test. The sooner they get to the receiver, the quicker they will be graded and returned.

Step 15: Upon return of the tests, make sure all scores were recorded correctly on the Score Sheets. Enter the test scores in whole numbers only (drop any fractions) in the Fall Score column of the ALL STUDENTS. Here, you may need to delete students who registered, but did not test. After all grades, are entered CLICK FILE/SAVE.

Step 16: Still on the All Students sheet, highlight the entire worksheet by going from A1 (Test Level Cell) to the last cell on the bottom right while holding down the left mouse button; this should “gray” the cells. Then go to the menu on the top and LEFT CLICK on DATA/SORT; in the first box click on TEACHER, the second box click on TEST LEVEL, and the last box click on the STUDENT LAST NAME. The HEADER radio button should be selected. CLICK OK and ENTER.

Step 17: Highlight all the rows with the same teacher and then to go MENU/PRINT. Click on the SELECTION radio button, because you want to print only that teacher’s set of names. Print one copy (or more if you need some extras). (Use your print preview—the little white page icon with the magnifying glass--to see if will print on one page). HINT: You can hide columns that you don’t need by Clicking on columns F, G & H; select MENU/WINDOW/HIDE. Print the teacher page. REMEMBER TO UNHIDE WHEN PRINTING IS FINISHED! Use this list to pull the tests for each teacher.

Step 18: Distribute tests to each teacher with a note that sets a deadline for re-grading requests. They should return tests to you within 2 or 2 ½ weeks with a note about the grading/addition or whatever problem. You send them to me ASAP, because deadlines are January 15^th and April 15th. I will return the tests to you with a letter stating my decision about the grade. If it were changed, PLEASE enter the grade on your All Student Worksheet in red.

Step 20: Let’s get back to work! We now need to get ready for the Spring Tests. We created Score Sheets Spring 06 workbook. Open that file.

Step 21: Go to Steps 5-19 and follow instructions from there, but use Score Sheets Spring 06.

Step 22: Make sure you enter the Spring Test Grades in Spring Score column on the All Students worksheet Spring 06. Keep track of any Test sent for re-grading (you might put an * next to the original score to let you know you need an answer before grade is final).

Step 23: ON TO SORTING MEDALS. Highlight all of the All Students page by holding down the left mouse button in cell A1 and going to the last cell on the bottom. Your sheet should be “grayed”. On the MENU bar, select DATA/SORT and choose Spring Score as the only sort and click OK.

Step 24: On the first row of names, assuming a student earned 98 or higher, type Gold in the Medal column. After entering Gold, remember the small white block on the lower right side of the cell, the handle? LEFT CLICK on the cell and drag straight down the column till you have added Gold to all the students with scores of 101 through 98. Review to make sure none of the students took the test below grade level; if so, remove Gold in the Medal Column. Also, check to see if any of the students were either vocalists or instrumentalists, because they won’t get a Whitlock theory medal. Type in the word Vocal or Instrumental, and review the TMTA-SA Handbook for Vocal and Instrumental medals.

Step 25: Do the same for the Silver and Bronze Medals. Silver scores are 97 through 94 and Bronze 93 through 90. Again, delete the medal if student tested below School Grade and type in Vocal or Instrumental for those qualifying. SAVE and leave open.

Step 26: OPEN Fall Worksheet/All Students. Go back to Step 23 and repeat instructions for medal sorting. SAVE.

Step 27: Now we are going to Copy the Fall Worksheet/All Students data into the Spring Worksheet/All Students file. Highlight the entire Fall/All Students worksheet below the Heading row 1. RIGHT CLICK on the mouse and choose COPY. DOUBLE CLICK to display the Spring Workbook/All Students and go to the bottom of the Spring data to the first open cell in Column A. If you had 67 students take the test in spring, you should be in Cell A69 (67 rows plus the header for Spring Data). RIGHT CLICK and select PASTE SPECIAL/VALUES.

Step 28: You should have both Fall and Spring students, their test scores and Medal type on one worksheet. Now we need to compare all data and remove any duplicate medals for any student. Highlight all the data and go to MENU, DATA/SORT; select Test Level first, Student Last Name second and Student First Name third; click OK. SAVE

Step 29: Search for duplicate names. Type the lower score into the correct column on the row of the higher score. Delete the row with the lower score, so you have only one medal for that student, but you have both scores on one worksheet. SAVE.

Step 30: At this point, you’re looking for someone to take the job next year, but stop and consider: It will be much, much easier the 2^ndtime around, not to mention the 3^rd and 4^th!! Make one last check to see that no medals were awarded to students testing below School Grade and that all Vocal/Instrumental medals were awarded at the proper grade level.

Step 31: You may fill out a Medal Order Form (F4) online. This is in PDF format. Fill it in and print it for reference later. OR better still: Use the F4 Medal Order Form on this disk which is an Excel form. You can save this form and make changes to it, if needed after you receive the TMTA test scores printout. When you and the office agree, fill out the F4; email it to me for approval. I will email approval back to you. At that point, you send your association check to the TMTA Office with the MP Form, emailing copies of the MP Form to me, to the VPSA and VPBA. When I know your check has reached the TMTA Office, I will order your medals. You should have them within 2-3 business days.

Follow the Instructions for Medal Distribution to avoid any “mess ups”. These instructions are available online or on this disk.

THANK YOU for your careful attention to the minutiae of the theory program. We could not do without you.