Generating List Based on Cell Value

[teacher1]

I'm creating a workbook for keeping track of attendance for a rural public school I'm working at in a developing nation. This is excel 2010, and I am a novice at excel. I've tried searching all over the internet but can't figure this out. I have two related things I want to accomplish:

Generating Student Absence Lists
I have four worksheets "freshman", "sophomores", "juniors", and "seniors". Starting at A6, column A lists student names. Starting at B6, column B gives the total number of absences that student has. What I'd like to do is have another worksheet, "principals report" which generates a list of the names of students who have met certain absence thresholds. So there would be 5 lists:
-One for all students who have 2 absences
-One for all students who have 3 absences
-One for all students who have 6 absences
-One for all students who have 9 absences
-One for all students who have 12+ absences

The lists need to spontaneously update as each day the counselor will put in new attendance information and print off the list.

Generating Teacher Submission List
On the same workbook there is another sheet called "Teacher Submission Tracker", where column A (starting at A6) lists teacher names, column B (starting at B6) gives the total number of days that the teacher has failed to turn in their attendance, and column C through HK (starting at C6) is a calendar where each day the counselor puts a "0" if the teacher turns in their attendance, and a "1" if they fail to turn in their attendance. So for instance C6 is for Aug 1st, D6 is for Aug 2nd, and so on. What I'd like (either on the same worksheet as the student absence report or on a new worksheet) is to generate a list that can be printed each day:

A list of the names of each teacher who failed to turn in attendance on a given day. So like check column C from the submission tracker and list any teacher name that has a "1" for the Aug 1st report. Then the next day change it to check column D to generate an Aug 2nd report, then the next day change to column E for the Aug 3rd report... and so on. Its ok if we have to manually change the formula each day from C to D to E or whatever... but hopefully in a way that is very easy to do for someone with essentially zero excel ability and highly limited computer proficiency in general.


I apologize for the long post. I'm novice enough at excel that I don't know how much or how little detail to provide, so I tried to be thorough. I really appreciate any help you could offer. Even just a link to a place where I could figure this out myself would be fantastic... I had a hard time googling around on this issue since I wasn't even sure how to describe what I wanted in a searchable way.

 

[ca.moazzam]

Hi can you provide a copy of your worksheet for quick help?

 

[teacher1]

moazzam:

Yes, please see link below (I hope its ok to post a DL link). Of course "student1" "student2" etc. and "teacher1" "teacher2" etc will have their actual names in it once we get our rosters.

Thanks so much for any assistance. On the "principals report" sheet feel free to change it in any way you like, so long as I end up with something printable.

FileSwap.com : Attendance Tracker.xlsx download free

-teacher1

 

[ca.moazzam]

Unfortunately that link is not working.

 

[teacher1]

Oops. I'm not sure what the problem is but try this one:


file storage

 

[ChrisR]

Hi Teacher1,

I think the easiest way for you to do this, would be to create several 'Pivot-Tables'

Are you familiar with Pivot tables at all?

If you go highlight your student data --> Insert --> Pivot Table --> Ensure the range is correct and select a new sheet (The sheet where you want the data combined)

You can then drag the various fields into the Pivot table

So you would drag student name across to row labels.....

The drag attendance (Or number of days off or whatever it is called, into values)

This will then list all students and their number of days off.

Then if you click on the top drop down at the top of the actual pivot table itself you can then apply filters (E.g. attendance days off greater than 1 etc)

Once this is set up, all you need to do to update it is right click and click 'Refresh' and it will instantly provide you with an updated list.

Therefore, you could create several of these (If you did not want to manipulate 1) and then just do several right clicks and 'refresh' and you will instantly have updated lists for your your data.....

I'd say this would be the easiest way to achieve what you are looking for.

Regards,
Chris

 

[teacher1]

ChrisR:

Thanks so much for the tip! I've got it working beautifully for the daily student attendance report.

How do you think I should work it for the teacher submissions though? What I'm looking for is a daily list which would give the names of any teachers who failed to turn in attendance on that day. I can do this for any given day using the same Pivot Table method, but how could I make it easy to change the day so that the next day, the pivot table is based on a new data range?

So for example I'd like to list Teacher2 and Teacher4 on the 1st, nobody on the 2nd, teacher 8 on the 5th, and teachers 2, 3, 4 on the 6th.

Right now I made another worksheet with the teacher names and absences on a given day, and each day the counselor would have to update the data by changing the column (say from C to D). That would mean both changing a letter in a formula, and dragging down to update all the data, then refreshing the pivot table. Thats getting borderline too complicated for the counselors... but if there is nothing better I guess it will have to do.

Can anyone think of an easier way to accomplish the list of teachers who are missing attendance?


Thanks for the help!

 

[ChrisR]

ChrisR:

Thanks so much for the tip! I've got it working beautifully for the daily student attendance report.

How do you think I should work it for the teacher submissions though? What I'm looking for is a daily list which would give the names of any teachers who failed to turn in attendance on that day. I can do this for any given day using the same Pivot Table method, but how could I make it easy to change the day so that the next day, the pivot table is based on a new data range?

So for example I'd like to list Teacher2 and Teacher4 on the 1st, nobody on the 2nd, teacher 8 on the 5th, and teachers 2, 3, 4 on the 6th.

Right now I made another worksheet with the teacher names and absences on a given day, and each day the counselor would have to update the data by changing the column (say from C to D). That would mean both changing a letter in a formula, and dragging down to update all the data, then refreshing the pivot table. Thats getting borderline too complicated for the counselors... but if there is nothing better I guess it will have to do.

Can anyone think of an easier way to accomplish the list of teachers who are missing attendance?


Thanks for the help!

Hi,

Put the date into the 'report filter' section then you can just select the date you want.

Regards,
Chris

 

[teacher1]

Hi Chris,

Thats a great solution for changing the date of the report... but when I do that I end up having to reapply the value filter each time so that only teachers who are missing are listed. Is there a way to get the value filter to stick when I change the report filter?


Thanks

 

[teacher1]

Hi Chris,<br><br>Thats a great solution for changing the date of the report... but when I do that I end up having to reapply the value filter each time so that only teachers who are missing are listed.  Is there a way to get the value filter to stick when I change the report filter?<br><br><br>Thanks