Inputs from drop downs automatically updating in a table

Hopefully you can tell me if this is possible. If it is possible hopefully you amazing people can help me out. I'm pretty sure it is and I've seen things like it before. I've tried to use google fu, but I don't really know the right questions to ask to get to the right resources.

I have a table with drop down menus. 16 people (for our office) will go into this excel and input 6 cells of information. I am trying to create a table which will automatically update information based on 3 of these cells (1, 5 and 6). Each person will have more than one entry and there are not specific timeframes for these entries, so I need the updating to be flexible.

I've been looking through the various posts, but can't find a question that is similar to what I'm looking for. I will attach a picture below which will hopefully help explain what I'm looking for.

The red portion are the preset areas that people will be entering information into. These options are basically all going to be drop downs except the final column (Hours/Credits)
The orange portion is going to be the inputs by the personal in the workcenter. These are all drop down menus as I said.
The yellow portion is the headers of the table that I need to be updated. The first column should be the same as the first column of our inputs Rank/Name. The rest of the columns are based on the possible options of the Type (5th) column. There are 13 choices for this column so 13 headers. The list for these is labeled "Type" and I copied/pasted it (transposed) so I know they match up.
The green portion is the area that I need to update automatically based on what users put in the orange portion. Obviously I typed this in myself, but that is what I need it to look like (or something similar to that). Notice that Capt Dude entered multiple Seminar's (6 and 4) and in the table it automatically added these together.

ExcelPic.jpg

This should be possible. Can you please post a workbook instead of an image?

TrackingCEsSanitized.xlsx

Hopefully I did this right and this attachment works.

There are other things I want to do with this table once I get it updating. I'll post that here just in case, but I plan on researching that once this issue gets fixed. The different 'Types' have maximums. So I want to figure out how to make that show up in the table by either changing the color of the cell or something else.

For instance the Type of Seminar has a max of 10 for Security+ (or SecurityPlus as I have it labeled) and a maxi of 6 for Network+. If the person is at 4 maybe it would be green, if they match the max turn it to yellow and if they go over red. Just so you can quickly notice that you will only get credit for part of those.

I highly recommend using a pivot table, like in the attached file. Definitely the easiest way to get what you are looking for.

I would do a bit of research on Pivot Tables, but it is pretty simple.
Create a Pivot Table, put Rank/Name in the Row Labels, Type in the Columns Labels, and Hours/Credits in the Values.
Push the refresh button every time you add new data to update the pivot table.