An Excel-illiterate teacher asks in desperation:
I want to link cells from a weekly school timetable (including several separate classes) to create a list of each lesson per class.
At the moment the weekly timetable runs left to right: the columns are headed by the day and date (Monday to Friday), while the times of each lesson form the first cell of each row. The timetable is repeated left to right to cover a whole semester, so that I can see when lessons are cancelled due to holidays or meetings or when a particular class has a test.
I'd also like to create a list of all the lessons for each class (such an overview makes semester planning for each class much easier). If I enter a cancellation or other information into the timetable, the corresponding lesson in the list should be updated.
The trouble is, that at the moment I only seem to be able to link each cell manually and individually, as the cells in the timetable are not contiguous.
For example: Class 1d might have a lesson at 8 on Monday, 10 on Tuesday and 12 and 1 (i.e. a double lesson) on Friday. The corresponding cells in the timetable are, say, B1, C3, F5 and F6. For the second week of the semester: G1, H3, K5 and K6. And so on for each week of term. Is it possible to create a series of links (=B1, =C3, =F5, =F6, =G1, =H3, =K5, =K6 etc etc) in a single row without doing it manually for each of the cells involved (given that I teach several classes, there are hundreds of lessons per semester!)? I've tried using OFFSET and the fill handle but can't get anything to work...
Bookmarks