Graduation Requirement Checker

[DentonHTHS]

Hi everyone,

Relative newbie to excel and I have been given the task in the next school year to come up with a way to cross-reference data to create a high school graduation requirements checker. I have data on the student's current history for each year they have been in the school and the list of requirements needed for graduation. I am unsure how to start this off in Excel. I really don't want to use Access as I really have no knowledge there and the school program isn't that friendly but I can shake loose data that will go into excel.

Any suggestions on how to approach the project? I have a general working knowledge of Excel in terms (I can use formulas, pivot tables, VBLookup).

Any help would be greatly appreciated.

 

[PaddyD]

hi - welcome to the board

how about describing this:

"I have data on the student's current history for each year they have been in the school and the list of requirements needed for graduation."

...& the checks you need to perform in a bit more detail...

 

[DentonHTHS]

Thanks for the quick reply.

Okay, the data itself is an academic history of the student for each year they have been in our school. So a grade 12 student would have all the courses they have taken in grades 9-12. The info includes the student name, their current grade level, course names, the associated final mark (regardless of whether it was a pass or a fail), the instructor and the attendance for the course.

The graduation requirements break down into two groups. Required courses such as Grade 11 Modern History and English and total numbers of electives in category groupings such as 5 credits at the grade 12 level and a computer literacy course.

I spoke to another person who was doing data work for another school who used Excel for the same thing but he was unwilling to share his secrets so I am hoping someone can point me in the right direction.

Thanks again.

 

[DentonHTHS]

Anybody with some basic thoughts of things I could try? My thinking is that I could set up all the courses in a pivot table and use VLOOKUP to check for the required courses. That would be a lot of tables but its possible I think. Its the "I need so many grade 12 courses" situation that I am unsure of. How would I set up something that would look for 5 courses at the Grade 12 level?

Wise and powerful gurus, lend me your brains!

 

[DentonHTHS]

Hi folks,

I realize this is a rather big project I am under taking so I am muddling through some initial ideas which I hope to bounce off people here.

I have set up one worksheet called Graduation Requirements that has the list of courses required for graduation listed across columns B-BB and all the students at the school listed in column A.

In a second worksheet I have all the courses taken by every student still at the school and their final grade. This work sheet is called Ac_Hist.

What I would like to do is have Excel find the course in Column B(and all the others eventually) and the student name in Column A and go look up the data in the Ac_Hist sheet. If it match the student with the course then it should return a blank cell. If it does match the student and the course it returns the value in the cell IF it is over 60.

I think this could be done with a combination of VLOOKUP and IF statements but I am a little out of my depth. Can anyone help me out on the best way to proceed? If so, I would greatly appreciate it.

Thanks very much!