Setting up a project

Hello,

I'm an elementary school teacher, and this year I'm in charge of keeping behavior data with in our school.

My approach to this project was to use several workbooks:

1. One workbook with a master list for the whole school.
2. A separate workbook for each teacher to record there student's behavior. The student's names would be linked to the master workbook list, so I don't have to keep coping names.

I need to know:

1. Is this the best way to do this in excel?
2. I need the list to be able to reference rows as I add and delete students. I'm not sure which formula to use.

Any suggestions would be great, Thank you!!;

Thank you for the help.

I'm in a small school (4th through 6th grade). So the reason I wanted to use separate sheet is that we will record data for behavior each week. (about 35 weeks) Do you think it would be better to have the weeks just keep going off to the right, and have a tab for each teacher? (9 teachers in all)

I didn't realize that more workbooks slow down excel. I was also going to use a separate workbook as a summary book to make the reports out of.

Thanks again,

Andy

Thanks for getting back to me.

Yes, each teacher will keep track of the students behavior each week. I will be inputting the data into the spread sheet, and making reports though out the year. So each teacher will have about 175 days worth of data. The last teacher who did this made each month a tab, again it makes for a big workbook.

Also, as you know, kids come and go, so the list would have to be updated. That is why I was looking into one master list, so it could be updated once, and link up to the other sheets.

Thanks for the example, that really helps!

My school is small, about 300 students total.

1. So a list of the student's name, the section number (determines what teacher they have) male, female, race, etc on one tab.

2. List of the teachers on another tab,

3. List of the behavior one a third tab (this will have to scroll to the right for all the months of school)

4. Table and summary on the 4th tab

I'm getting this. I can sort the grade levels by section, then name, so it will be easier to enter the information each day.

I should have mentioned that I am using 2003 (my school is anyway) should have stated that before.

Thank you,

Thank you Marivn!

This is what I need. We are entering data at the end of the week. Each point falls under a category: Respect, Responsibility, etc. There are different codes under each one:

Forgetting homework would be a point, but under responsibility
Taking back to a teacher would be under respect

etc.
So I need points for a week, listing the code for what the student did, and then under a category where it falls.

I have to review the pivot table again.

Thank you

Ok,

Attached is a format I may have to work with this year.

I'm not able to make a pivot table out of this:

Each day, students start fresh with 7 points. So I need to track points for each day, and there are 7 ways to get points.

I also need to keep track of the behavior. That is on the code tab

The summary will keep track of the points for the month.

I need to break this data down to grade level, gender and by teacher. I don't think I have this table set up correctly.

Any help is welcomed!

Andy;

Marvin you are a big help, I really appreciate your input!

At my school each day, a student can earn up to 7 points. If they earn 7 they are suspended.

So that's why I set up the column like that, so I can place in the points for that student. Each teacher (9 in all) will have a point sheet (I attached the one we are using) so they can track there student's points for the week.

I take that sheet and enter the data.... so if a student "Bob" has a bad day... he may have 2, 3, 4, or 7 points on Monday. However on Tuesday, he will start fresh with no points.

I was also trying to keep track of students with no points as well (it's always good to publish the numbers of your good students, not bad) and students who behave also receive awards as well.

That's why this set up was getting to me. So if I follow your suggestion, I each week I would have dates, (or a date at the end of the week) the student's name, code and behavior. The following week Date, code, Behavior, etc.correct?

So I was trying to list the student once and the behavior each week, so I should list the student each time there was a behavior issue.

I think I'm getting this... So how do i'm not clear out I enter students each time I need to add there name, drop down?

Thanks again,

Andy

Oooohhhhh wait.... I wouldn't go to the right, I would keep adding students down. Duh....

Marvin,

The set up you suggested will work. I have hopefully the last two questions about this:

1. I need a way to see what students have NOT received any points. So is there a way to sort or filter who has not received any points?

2. Can the pivot table view date ranges? I need to have a range of data for each quarter, or would I just select the data in that range when I set up the table?

Thank you again, this saved me this year!

Thanks Marvin,

I tired the countif formula, however it appears I would a have to add each name in the range. I was hoping for a quicker method. (I may be doing this wrong too) However if I enter each student, I can just view another report to see the results. I guess I don't want to key in 300 names. (lazy I guess)



I like the expanding ranges, and I have to check out the dates, I was just going to sort by dates but your method will work great.

Thanks again,

Andy

Hey Marvin,

Attached is the first week of keeping track of behavior. It is a little time consuming entering data, but the table works out great. I attached a copy.

I have a question(S)

1. I'm not getting how to do a date range with this. I just entered the last day of the week and the behavior. I don't get the data until the end of the week, and trying to figure out each day as I was entering data was not working. I need to figure out how to group data by weeks, or quarters.

2. Is there a way to automatically add a cell function to the next row when I hit the enter key?

Thanks a lot for your help!

The attachment didn't take... what gives?

Let me try this again...

Hi Marvin,

Thanks for getting back to me. I will try the date set up. I thought I had the last and first names set up as a v look up. I type in the full name and it finds it. I didn't even think of copy and paste...

I'm almost wondering if this is a better job for filemaker pro... maybe I'll try data basing this thing too.

Thanks again,
Andy

Hey Marvin,

Well it's been three weeks now and the system you helped me set up is working out great!

I attached a copy it. My principal likes the reports, as does the staff. I make a complete one for the principal, and a class report for each teacher.

Now I have to do more with this... I hope you can help:

I need to be able to see every month, and every card marking which kids don't have any points.

I also need to account for students who move. I don't just want to delete them from the system, I need to subtract them from the main count on the class totals tab. Students who move have to be removed form the total count, but also from the teacher's class count.

Any advice would be great.

Thanks,

Andy

Hi Marvin,

I think I'm getting this.... Adding the Active and inactive worked great.

For students without points I think I have it: I used the auto filter under the data tab to filter out the months I want. Then in the student tab I used a countif function, it seems to work as long as the range is set to the area that is shown after the filtering.

I'm not able to pick more than one date on my excel. I'm using a mac at home with 2008 (Getting the upgade this weekend) and work is still on 2003. However I think I can use the same procedure and make a new piviot table.

Thanks for you help.

Andy