Summarizing Data on one worksheet from multiple worksheets in same workbook

[K Howe]

I set-up a workbook with multiple worksheets to track monthly attendance. There are worksheets for months 1 - 8 and a summary worksheet to summarize the monthly data. I need a formula that will copy the number of days attended for each student from the monthly worksheets to the summary worksheet.

The students listed on the monthly worksheets may vary due to new students enrolling or current students exiting.

The summary provides a list of students who have had attendance at anytime during the year and will also vary due to new students enrolling or current students exiting. If a student leaves that students name stays on the list. If a student enrolls the students name must be added to the list.

The problem:
When a new student is added or a current student leaves the cell address for days of attendance for that student changes on both the summary and monthly worksheets. So far, the only way I can summarize the days attended for each student is to click on the cell for the month I am updating, press the '=', select the monthly worksheet I am updating from, click on the cell that has the days attended for that student and press enter. Its a very time consuming process and accuracy is a problem. I do this for four different schools.

Your help is greatly appreciated.

[Pete_UK]

Take a look at the SUMIF function in Excel Help - i.e. add the days from a cell if the corresponding cell in another column matches with the name.

Hope this helps.

Pete

[FDibbins]

I saw a very neat trick for summarising multiple sheets, it works better if the layout of each sheet is the same, but can be adapted for if its not.

Start and end with a blank sheet, call them "start" and "end". have 1 final sheet called summary (or whatever you want to call it) before "start" or after "end".

In your summary sheet, your references are always =function(start:end!A1) for instance. If you need to add or remove sheets to your sheet range eg add a month, remove a week, all you have to do is move it into, or out of, the "start":"end" range of sheets, and your formulas will automatically accomodate the change.

If you can provide a sample of what you are trying to do, Im sure we can get you started on what you have in mind