Exercise 5: Creating an Automated Schedule

Objective: Create a basic template for an automated schedule that uses basic cell formulas to transfer values from one cell to another.

Final product will look like:



1. Open Excel and a blank worksheet.
2. Type the following Labels and values: (Don't Forget to make Labels Bold.)



3.  Type "Monday" in cell C15.  Make "Monday" Bold.
4.  Type "Tuesday, Wednesday, Thursday, Friday" in cells D15 to G15.
5.  Type "=$B$2" in Cell A15. Make Cell A15 Bold.  Now Cell A15 should say: "Insert Grades"
6.  Type "=$B$3" in Cell A16. Now Cell A16 should say "Insert Time"
7. Your spreadsheet should look like this:



8. Highlight Cells C16 to G16.



9. We will now outline these cells with a border. Right click on the cells and select "Format Cells."
10. Click "Border," "Outline," and "Inside."  Then click "OK."




11.  Your spreadsheet will now look like this:




12.  Highlight cells C17 through G24.
13.  Right click and select format cells.
14. Click "Borders" and select "Outline" and "Center Line" icon.  Click "OK."



15.  Your spreadsheet should now look like this:



16. In cell B17, type "=B5." 
17. Use "Autofill" to fill cells B18 to B22.
a. Left Click Cell B17
b. Hover mouse over lower right corner of cell until it turns into a black "x"
c. Left Click and drag through cell B22

18. Your spreadsheet should look like this:



19. Type the first day of school "06 Aug 07" in cell C16.
20. Use autofill to put dates in cells C16 to G16.
21. Your spreadsheet should look like this:



22. We will now make the second week of school.  We will simply copy and paste week 1 with all the formatting.
23. Select cells A15 through G24.
24. Right click and select "Copy"
25. Left click once on cell A26.
26. Right click cell A26 and select "Paste."
27. Your spreadsheet should look like this:



28. We now need to adjust the dates of the week and the specials list to the left of the week.
a. Type "13 Aug 07" into cell C27 and use autofill to complete the rest of the week.
b. Type "=B5" in cell B28. Use auto fill like step 17 to fill cells B28 to B33.

29. Your schedule should now look like this:



30.  Time to save your work!  Use "File-Save" and save your schedule as "Lastname Exercise 5."

31. We will now make the "teacher name" matrix.  This allows us to auto fill the teacher names into the weeks. 
32.  Left click on cell I16.  Type "=D5"
33. Use auto and fill Cells I16 through I21.
34. Left click on cell I22.  Type "=D5"
35. Use auto fill and fill cells I23 through I27.
36. Your spreadsheet should look like this:



37. Left click in cell J16. Type "=I16" Use autofill to fill cells J17 to J27.
38. Your file should look like this:



39. We will continue this pattern.  Put "=I16" in cells K18, L19, and M20. Use autofill to complete down to row 27.  It should look like this:



40. Type "=I21" in cell J16.  Use autofill to complete cells K16 to M16.



41.  Highlight cells J16 through M16.  Use auto to complete the rest of the matrix.



42.  Now its time to fill in the weeks using the schedule matrix.  Left click in cell C17 and type "=I16"  You should see a "T1" appear.



43.  Use autofill and complete cells C18 through C22.



44. Use autofill again and complete cells D17 to C22.



45.  One more week to go.  We will repeat the process for the week dated 13 Aug 07.  Left click in cell C28 and type "=I17." You should see "T2" in the field.  Use autofill to complete the week.



46.  Now the magic begins!  You have created a 2 week schedule that will automatically arrange teachers and specials for a 6 day rotation.
Go to the top of your spreadsheet and click on cell B2.  Type "5th Grade."  Notice that "5th Grade appears in cells A15 and A26.



47.  Select cell B3.  Type "1:00-1:45."  Again, cells A16 and A27 have the time.
48.  In Cells B5 through B10, Enter some specials like PE, Music, PE, Art, PE, Computer.



49.  Almost done!  Enter some teacher names in cells D5 through D10.  These names will automatically be entered in the two week schedule.



50.  You are DONE!!!  Notice that each teacher rotates through their specials in a 6 day rotation.  To complete an entire year, you copy and paste blank weeks and then use autofill to distribute the teachers.  I make a template for 6 day, 7 day, and 8 day rotations and them use them for the different grade levels.

51. Save your work!