Tough problem! How can I average different columns based on student names??

Hey everyone I have a really tricky problem, I'll try to be as clear as possible and I know I've been really close to the answer.

I'm trying to create more of an automated grade tracker for my school.

The first problem and can be totally bypassed if need be; having a list of students automatically generate on tab 2 based on student names and class names in tab 1, without having skipped cells. Yes confusing, I included pictures.
Example: Tab 1 has student names, class names, and grades for all kinds of different tests as the columns. This will be a tracker for multiple grades and classes. I want a list to generate on tab 2 that sorts them into class lists. Tab 1 allows me to vlookup grades and I want tab 2 to average only specific test scores which....brings me to

Problem 2; I'm having so much trouble averaging only certain columns based on student name. I honestly felt like a pivot table was going to solve this, but i only want two columns (student name and the average of specific tests). For example the average of test 1 and test 3 for based on student name. I've tried many versions of averageifs along with the combination of vlookups. I'm really struggling here. Any ideas???

Hey guys thanks for the quick response. I'm going to post the excel file because there isn't any sensitive info just yet, all the grades and class names are made up right now. Also, ignore the ummm color choices for now, it was just to differentiate.

What I currently have happening is teachers will paste their data on the "NHM Data", "Spelling Data" and "NS Data" tabs, then I want almost everything else to autogenerate. I may have been thinking about this incorrectly, so please just let me know if I was way off and there's actually an easy way to do this.
I wanted the "Growth and Analysis" tab to autogenerate student names based on class and then also autogenerate averages based on the test name. For instance Student x would be placed under Oneonta and then it would average their NHM scores from column E, H, K, etc...throughout the year. I changed this slightly in the "Academic Other" tab because the test types weren't listed next to eachother and I thought it might be easier that way.

This is all aimed at creating the least amount of work for the teachers. If I can have these averages autogenerate I will be able to do a ton more analysis for them. Like I said, I feel like I was really close to the right equation. First thing is I don't know how to populate the list of kids under each class without leaving spaces. And then I don't know how to get the averages of only NHM tests based on the student names.

Any ideas? I'm going to try and use your answers for the time being and see if that will help me.