Excel Question (how to make layers?)

[Mike Lowe]

I use Excel for my attendance sheets as I like how it tallies absences and tardies automatically the way I created it.

My question is that after about 3 weeks of a semester it is time to move to a second sheet. I can create a duplicate sheet but it gets messy trying to keep the tallies of absences and tardies between the two sheets.

What I'm hoping to do is create a second set of 3 weeks which could sit "on top of" the original first 3 weeks. I'd like to do so in a way which would keep the tallies accurate.

Ultimately, I print a hard copy of these sheets out each week and want to keep each class down to one sheet: the way I have it now.

I'd upload an example but I am not legally allowed to post student names in public etc.

For more detail, the sheet has the names on the left, followed by M,T,W,T,F 3 times. Finally, at the far right, are the tallies. This fits perfect onto one sheet. I'd like to make weeks 4-6 take up the exact same space while keeping say the 4th absence of someone (but first on the second layer) tally as a 4th absence and not 1st.

Make sense? There has to be an easy way to do something like this right?

[JonInMiddleGA]

If you don't want to print the entire class endlessly on one sheet (I mean, you eventually run out of room left to right even if you're doing this manually), then one way to accomplish what I think you're shooting for would be to simply Hide the first three week set and add the 15 columns for the next 3 weeks between those and the cumulative total, which would leave you seeing weeks 4-6 and the correct total, retaining the others for tally purposes but making them invisible.

Make sense?

[Mike Lowe]

Jon,

That sounds cool. How do I make cells invisible? The reason I don't want to have multiple sheets, which normally would be fine with me, is that the names of the students would ONLY show up on the first sheet (unless there's another way I'm not realizing...which is possible as I'm not an Excel pro).

[JonInMiddleGA]

Highlight the columns you want to remove from view, right click, select "Hide" ... Unless you're on 2007 and they've moved the command, in which case I'm sure you can do it but someone else would have to point you to it.

You'll know it worked when you look at the column headers and notice a gap between column B (or whatever) going to like column L. To unhide later to see everything, simply select one column to the left through one column to the right of the hidden area, right click and select Unhide.

[DaddyTorgo]

mike - just select the columns and "hide" them. i have open office and not excel on my laptop here at home right now, so i don't recall what menu it's in

[FrogMan]

if you want to have the rows easily accessible, you might use "group". Go to "data" and then "group & outline". Grouping rows together will add a clickable "+" sign in the margin of your spreadsheet. Said "+" sign will change to a "-" when the group is expanded...

FM

[FrogMan]

dola, works for columns as well, of course.

FM

[CU Tiger]

1) You can make the names print on each page as a title cell.
2) Why not carry the total over to a 2nd sheet if it works for you
IOW:
A - Name
B - Carry over total [value = Sheet!1 G2]
C - Monday
D- Tuesday etc.
Y- SUM (B:X)

[Mike Lowe]

Originally Posted by CU Tiger

1) You can make the names print on each page as a title cell.
2) Why not carry the total over to a 2nd sheet if it works for you
IOW:
A - Name
B - Carry over total [value = Sheet!1 G2]
C - Monday
D- Tuesday etc.
Y- SUM (B:X)

I tried searching (2007 version) for "title" and "title cell" but to no avail. I do think this would be my best option as when I go with the hide option, it deletes the seating chart I have laid out below the attendance grid.

[pbot]

As follow-up to CU Tiger's post....

Your original document with your setup is on sheet one of your excel file. It should be your form template without any actual attendance data (yet), just the data labels (M,T,W,TH,F) across row 1, and the names down the side in column A.

-Rename that sheet "January" (or whatever you want to call it). Make a copy of "January", and paste it on to sheet #2.
-Rename sheet #2 "February" (or, again, whatever you want). Make a copy of either "January" or "February" since they should at this point be the same form template and past on to sheet 3. Rename sheet 3 to "Year to date" (or whatever).

So now, you have names in column A, with your daily (M,T,W,TH,F) headers across row 1 on all three sheets. The first student's name will go in cell A2. And their attendance data will populate in cell B2, C2, D2,etc on each sheet.

On the "Year to date" sheet, put the following in cell B2..."=SUM(January:February!B2)". This will sum the B2 cell on the January and February sheets, respectively, and place the sum total in cell B2 on the Year to Date sheet. You can then just print out the YTD sheet for your one page summary.

You can use the same formulation for many excel functions (avg, min, max, etc) to get whatever value you need. Just make sure that you have the correct sheet names for the first and last sheets you want evaluated in the actual formula.