Help with attendance sheet???

Question

Level 1

0 points

Help with attendance sheet???

In the same file, I have multiple sheets : one for every Saturdays from January to June. With always the same students. Same time, etc. the only thing that changes is if they're attending or not. So for this part, I already have it figured out.

But now, at the end of the year I want to have a master sheet with every week and every student.

Ex. On sheet1 named "080111" I have all the names in C9:C18 and in D9:D18 I have the " yes or no" depending on if they attend or not. Now, on sheet named " Report" I have in B1 the first name which was on "080111" at C9. What I did was = 080111 :: Table1 :: C9. Now, I need a formula to retrieve all the other names that are in C10:C18 to put them in B1:I1. When I tried different formulas it would always increment it wrong...

i.e in cell C1 =OFFSET(080111 :: Table 1 :: $C$9,1,0) and then I would like it to increment to =OFFSET(080111 :: Table 1 :: $C$9,2,0) when I drag it to D1. I'm guessing either I'm using the wrong formulas on my formulas are wrong. That's my first issue...

2nd....

I also need to retrieve all the "yes" and "no" per week(sheet). So now, I have D9 on all the sheets for the first student that I need to put in "Report" B2:B26.

Simply clueless!!!

Please help!

Black MacBook Dec 2007, Mac OS X (10.5.8), Processor 2.2 Ghz, HD 500Gb, Ram 4Gb

Posted on Jan 3, 2011 9:49 PM

Reply

Answer 1

Jan 4, 2011 2:11 AM in response to Ray Keyz

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

Reply

Answer 2

Barry

Level 9

60,023 points

Jan 4, 2011 3:33 AM in response to Ray Keyz

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

Reply

Answer 3

Jan 4, 2011 5:45 AM in response to 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

Reply

Answer 4

Ray Keyz Author

Level 1

0 points

Jan 4, 2011 9:34 AM in response to Jerrold Green1

So thankful for your quick responses!!! This forum is great.

Maybe with images it'll be clearer!!

<table style="width:auto;"><tr><td>

</td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From Excel</td></tr></table>

As you can see in the first picture, this is my weekly sheet. so for this one I have no problem it's for the 2nd one that it gets more complicated.

<table style="width:auto;"><tr><td>

</td></tr><tr><td style="font-family:arial,sans-serif; font-size:11px; text-align:right">From Excel</td></tr></table>

To answer your question, the other teachers and I fill this weekly sheet and at the end of each saturday we give it back to the H.R dept. So it's just the way we've been working for so long.

As you can see in the 2nd picture, I need to be able to copy the names from sheet "080111" C9:C18 to my report B1:I1. Then copy D9 from all the sheets to my report on B2:B26. Then copy D10 from all the sheets to my report on C2:C26. and that with with all the other D11:D18.

I'm guessing I'll have to enter some data but I'd really like to automate most of the process with the right formulas.

Thanks in advance.

Reply

Answer 5

Jan 4, 2011 10:46 AM in response to Ray Keyz

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

Reply