constructing a table which will update itself when new records are added

I'm building a system to analyse the attendance patterns of students at a college.

I'm having difficulty structuring a table which holds attendance data by student, week, day, period and class, and I would like to know if I can quickly and easily create the table using copy and paste down, and if the table will automatically update when new students are added to a separate 'student_records' table.

I have several catagories of data which go into student records, but the ones that are important for attendance analysis are 'Student ID' and 'Class'. These two catagories are listed in a table called 'student_records', along with all the other students records (such as name, phone number, etc.).

Actually, the 'Class' is only necessary for teachers when their marking attendance, so that they can select a class and see everyone listed in that class, to make it easy to mark them as present or absent from that particular lecture. A student can attend any lecture being given by any staff member, according to the schedules. However, from what I understand of pivot tables, I will need to specify 'Class' in the attendance table, to build relationships and get attendance by subject and staff member.

There are two schedules: the subject/lecture schedule; and the staff schedule. There are seven classes and there are seven lectures taking place at any one time.

The table holding the data on attendance is structured with the following order of columns: Student ID - Week - Day - Period - Class - Attendance.

My idea was to have: Student 1 - Week 1 - Day 1 - Period 1 - Class 1 - Attendance(Yes/No) and cycle through the classes (1-7), then the periods, and so on. So there would be 13 weeks x 5 days x 6 periods x 7 classes = 2,730 entries for every student, and only one 'Yes' for any given Week/Day/Period.

I'm stumbling at the first hurdle though, because I can't create the attendance table, such that it takes Student ID values from the 'student_records' table, and places them in the table alongside each combination of Week, Day Period and Class.

And, if this can be done efficiently (or, if this is the most efficient was of structuring the data), how can I arrange it so that when a new student is added to the 'student_records' table, the attendance table automatically updates itself?

Is this possible/efficient?

I have tried copy and paste down, but I either get a relative reference for the student ID column in the attendance table, or Excel doesn't recognize the repeating pattern that I set up with absolute references.

Hi Chris and welcome to the forum,

You have a lot of questions above, which might have great answers with a little understanding of the features built into Excel.

First, read about and understand Dynamic Named Ranges. These will grow as you add new data.
http://www.bettersolutions.com/excel...G820716330.htm
It addresses your thread title about tables that update themselves.


Then I believe you need to understand how Pivot Tables can group and filter data to show answers that you want.
http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/

Lastly after working with the above and seeing how Excel deals with Pivots and DNRs you need to construct an Attendance TABLE that allows you to get the answers you need.

After looking at your attached workbook, you seem to be struggling with a table format. Simpler is my answer. Insure each column is a unique field. Duplicate data in the rows so they can be used in a Pivot.

Read the above two topics and see why they might fit and then post a question like: How many students were sick on the week of Feb 15th? Then see if your table construct can give an answer. Continue to pose these types of questions and modify your table structure and Pivot table structure to answer these questions. Then I think you will be on the way to solving your question.