Complete Excel newbie here.
I have a workbook consisting of two sheets: a Monthly Class Schedule (Divided into five one-week blocks. Each block's vertical is Mon to Fri, the horizontal is 9 class slots. There are 45 class slots pw). Each class has one student and the cells are filled 'Student Number, First Initial, Surname' eg, '666 J Smith'. Students may be scheduled for several class slots per week and some class slots have no students scheduled. When the student attends a 'P'is added at the beginning of the cell eg,'P 666 J Smith'.
The Student Attendance Report has the students listed vertically on the left, arranged by Student Number, and then a column for each week.
At the moment I have to manually count the classes each student has attended for each week in the Monthly Class Schedule and enter the attendance figures in the appropriate week column in the Student Attendance Report. It's a PitA so I want to automate the process by using the Student Numbers in the Attendance Report Sheet to search the week blocks in the Schedule Sheet for 'P S#' and auto-fill the week columns in the Attendance Sheet. I think I should be using COUNTIF but I haven't been able to get it working. Any ideas will be very gratefully received by myself and the other teachers. I can mail an example workbook if necessary.
I have a workbook consisting of two sheets: a Monthly Class Schedule (Divided into five one-week blocks. Each block's vertical is Mon to Fri, the horizontal is 9 class slots. There are 45 class slots pw). Each class has one student and the cells are filled 'Student Number, First Initial, Surname' eg, '666 J Smith'. Students may be scheduled for several class slots per week and some class slots have no students scheduled. When the student attends a 'P'is added at the beginning of the cell eg,'P 666 J Smith'.
The Student Attendance Report has the students listed vertically on the left, arranged by Student Number, and then a column for each week.
At the moment I have to manually count the classes each student has attended for each week in the Monthly Class Schedule and enter the attendance figures in the appropriate week column in the Student Attendance Report. It's a PitA so I want to automate the process by using the Student Numbers in the Attendance Report Sheet to search the week blocks in the Schedule Sheet for 'P S#' and auto-fill the week columns in the Attendance Sheet. I think I should be using COUNTIF but I haven't been able to get it working. Any ideas will be very gratefully received by myself and the other teachers. I can mail an example workbook if necessary.
