J
JEM
First, I'm sorry to have written a novel. But from what I've seen so far I
guess the planning stages are the most important, and if someone can offer
assistance, they need as much information as possible.
I want to create a schedule that automatically updates information in a
table based on information from various worksheets. Curently, this is all
done by hand for all grades and classes.
Basic info:
My school has class 22 day a month; Monday through Friday and the 2nd and
4th Saturday.
Each weekday has six periods, Saturdays have four.
There are three different grades; some grades have class one time a week,
others have class two times a week.
Grades that meet once a week have 27 classes; grades that meet twice a week
have 54 classes.
I've just started out and all I have made is a worksheet with the dates in
COL A and the class periods (1-6) in COL B-G.
In each row beside the date, I've put the proper class in the corresponding
class period.
I'd like the table to look something like a schedule; Sunday-Saturday across
the top and periods 1-6 in a column under each day. The information I need
in the table is what class number will be taught. They need to be counted in
sequence an the totals have to carry from month to month.
Example:
On Monday 1st period the H2A class has a class; so
April week 1, Monday - period 1 shows H2A Class 1
April week 2, Monday - period 1 shows H2A Class 2
April week 3, Monday - period 1 shows H2A Class 3
April week 4, Monday - HOLIDAY 1 shows H2A no class
May week 1, Monday - period 1 shows H2A Class 4
May week 2, Monday - SCHEDULE CHANGE period 1 (shows J1B Class 6 instead)
May week 3, Monday - period 1 shows H2A Class 5
May week 4, Monday - period 1 shows H2A Class 6
To make matters more complicated, because Saturday classes are only twice a
month, they are rotated into the schedule.
Sometimes Wednesday period 1-4 is replaced with Saturday period 1-4.
Sometimes Thursday period 5-6 is replaced with Saturday period 1-2, and
Friday period 5-6 is replaces with Saturday period 3-4
So, I need the cells in the schedule table to report the information this
way; they need to count all the times a class occurs to date and display the
count in a cell for a given day on a given period. Simply counting all the
instances that 'H2A' appears in the Monday, first period cell doesn't work,
because a schedule change may shift that class to another day or period.
So here's the question: can any one suggest a format for the worksheet
besides the one I mentioned above? Since I plan to make a pivot table to
display the information should I break it down a little more to make it
easier to build the table? I.e. Should I add another column for month and
week?
Anyway, I guess the first task is to get the basic schedule worksheet set up
properly. Second is to build a table to display the info (along with
formulas to get the info?) If I can get that far, just to have the class,
e.g., H2A to display in a cell, along with what number class it is that
would be great. After that, I'd like to get information from another table
that says what the contents of the class are, print, review, test,
presentation, etc.
guess the planning stages are the most important, and if someone can offer
assistance, they need as much information as possible.
I want to create a schedule that automatically updates information in a
table based on information from various worksheets. Curently, this is all
done by hand for all grades and classes.
Basic info:
My school has class 22 day a month; Monday through Friday and the 2nd and
4th Saturday.
Each weekday has six periods, Saturdays have four.
There are three different grades; some grades have class one time a week,
others have class two times a week.
Grades that meet once a week have 27 classes; grades that meet twice a week
have 54 classes.
I've just started out and all I have made is a worksheet with the dates in
COL A and the class periods (1-6) in COL B-G.
In each row beside the date, I've put the proper class in the corresponding
class period.
I'd like the table to look something like a schedule; Sunday-Saturday across
the top and periods 1-6 in a column under each day. The information I need
in the table is what class number will be taught. They need to be counted in
sequence an the totals have to carry from month to month.
Example:
On Monday 1st period the H2A class has a class; so
April week 1, Monday - period 1 shows H2A Class 1
April week 2, Monday - period 1 shows H2A Class 2
April week 3, Monday - period 1 shows H2A Class 3
April week 4, Monday - HOLIDAY 1 shows H2A no class
May week 1, Monday - period 1 shows H2A Class 4
May week 2, Monday - SCHEDULE CHANGE period 1 (shows J1B Class 6 instead)
May week 3, Monday - period 1 shows H2A Class 5
May week 4, Monday - period 1 shows H2A Class 6
To make matters more complicated, because Saturday classes are only twice a
month, they are rotated into the schedule.
Sometimes Wednesday period 1-4 is replaced with Saturday period 1-4.
Sometimes Thursday period 5-6 is replaced with Saturday period 1-2, and
Friday period 5-6 is replaces with Saturday period 3-4
So, I need the cells in the schedule table to report the information this
way; they need to count all the times a class occurs to date and display the
count in a cell for a given day on a given period. Simply counting all the
instances that 'H2A' appears in the Monday, first period cell doesn't work,
because a schedule change may shift that class to another day or period.
So here's the question: can any one suggest a format for the worksheet
besides the one I mentioned above? Since I plan to make a pivot table to
display the information should I break it down a little more to make it
easier to build the table? I.e. Should I add another column for month and
week?
Anyway, I guess the first task is to get the basic schedule worksheet set up
properly. Second is to build a table to display the info (along with
formulas to get the info?) If I can get that far, just to have the class,
e.g., H2A to display in a cell, along with what number class it is that
would be great. After that, I'd like to get information from another table
that says what the contents of the class are, print, review, test,
presentation, etc.