Hello. I'm trying to create my own streamlined grading process, and I really hate the software we're given for anything other than just entering the grades. It's a pain to keep track of everything. So I've gone about laying out what I want in excel, but it's still a bit over my head as to how to set it up the way I want, and any advice would be appreciated.

Here is a picture of the main sheet: https://i.imgur.com/LZSfrSO.png

As you can see it's pretty simple, with just the names, current average, and the assignments. On another tab I have the names of the grades and their weights, here: https://i.imgur.com/uJ19tKk.png

What I want is to have the cells labeled "assignment #" to be drop down menus that I can choose the assignment type from, which I could then name, and then have the entire column be that weighted assignment. So for example, column C from row 2 down would all be a quiz grade, with that proper weighting. Then, column B would be the averages of the students grades, summing all together their row of assignments, excluding anything without a value entered in it.

I've looked up how something like SUM or AUTOSUM would be useful features, but I can't find a way for them to help me here.

Any help would be great, thank you.