Help with attendance sheet???

Hi Ray,

Welcome to the Numbers Discussion.

If every Sheet's Table has the same list of names, why use a formula to retrieve them? You could just copy the list from one of the tables and paste it into your summary and be done with it.

Using the OFFSET function has a lot of charm in some instances, but here you would have been served just as well by a simple cell reference which would automatically adjust if you use Fill Down to extend it to other rows.

For summarizing the attendance report, take a look at the COUNTIF function. Since you are copying all the data to the summary, you should be able to do all the counting there.

Regards,

Jerry

Hi Ray,

Welcome to Apple Discussions and the Numbers '08 forum.

You are making the task more difficult by placing the student names in a column on the weekly sheets and switching the arrangement to the student names in a row on the master sheet. Is there a reason for this change?

To simplify matters, I will keep the students listed in a Column on all tables, and will start the list at Row 9 on all tables.

For next year, I would start with the Master table, a list of the Student names and any personal information you are recording. Student names in Column A, starting at Row 9.

From this I would construct Weekly tables. construct 1, then duplicate and rename it to make the others. Names on each of these tables would be transferred from the master table, using OFFSET(). Attendance for the week would be recorded by the user. Student names in Column C, Attendance in Column D, starting at Row 9.

The Report table would also use names transferred from the master Table, and would transfer the attendance data from the weekly tables using the same OFFSET function as above. Student names in Column A, Weekly attendance in Columns B, C, etc., all starting at Row 9.

Give each of the Weekly tables the same unique name as the sheet it is on (eg. 080111). This will shorten your formulas and make them easier to read, as with each table uniquely identified, the sheet name will be unnecessary.

Use $A$1 (on the appropriate table) as the base cell for all OFFSET functions. It's the easiest cell to establish an offset from, and although not necessary here, is in a header row and able to avoid being moved as the result of a sort.

Formulas:

In C9 of each weekly table, and filled down to C18: =OFFSET(Master::$A$1,ROW()-1,0)

In A9 of Report, and filled down to A18: =OFFSET(Master::$A$1,ROW()-1,0)

Note that the same formula is used in each case. This transfers the names from column A of the Master table to column C of the weekly table (use it for next year, as you already have these filled in for this year), and from Column A of the Master table to Column A of the Report table.

In B9 of Report, and filled down to B18: =OFFSET(080111::$A$1,ROW()-1,3)
In C9 of Report, and filled down to C18: =OFFSET(150111::$A$1,ROW()-1,3)

The first transfers the values from column D (3 columns right of column A) of table 080111 (renamed as described above) to column B of Report.
The second transfers values from the same cells of table 150111 to column C of report. The only change in the formula is to the name of the source table.

Regards,
Barry

Barry,

I'm glad you noticed, as I failed to do, the row/column transposition. That's what I get for looking at the forum when my eyes are only half open.

Ray,

What's the motivation for having a separate Sheet for each week? Just curious, and it's easier to understand and help if we have some of these details.

Jerry

Ray,

My first suggestion upon seeing your screen shots is that you adopt a Numbers-centric approach to your table design. Maybe you don't want to do that because it would make your document less compatible with Excel. You now have an Excel-like island of content in a sea of empty cells in that weekly sheet. In Numbers it make more sense to trim the table to just the size needed.

Your Names in the weekly table aren't in contiguous cells, as they are in the summary. for this reason, I doubt that you really want to do a transposition that includes the blank cells that I see in the weekly sheet. I recommend that you use a LOOKUP function to retrieve the week's data for each student.

For example, to grab the attendance data for Samuel Pierre on 08/08/11, you would write:

=LOOKUP("Samuel Pierre", 080111 :: Table 1 :: C5:C22, 080111 :: Table 1 :: D5:D22

Please make allowances for any error I have in your columns and rows because I'm guessing a bit at the addresses. You can automate the addressing a bit if you want to invest in programming, but this approach will do the job.

The addressing would be simpler if you did the layout the "Numbers" way.

Jerry