Calculating an inventory using Vlookup or some other function?

Hello all... I have been using this site for awhile now, and I am trying to figure out some formulas and having ZERO luck. I have attached the file so that you guys can see what I'm trying to accomplish. If I (you) can get this figured out.. i will be a rock star at my company!!!!!! ANY and ALL help would be greatly appreciated!!

So here is what I need to figure out...
Column A-F have been setup with drop down and dependent drop down menus which I knew how to do, Column J and K have the breakdown of all the chemicals that we use. When an item is brought into the inventory, the person would add a line, pick the date/vendor/desc/etc... Col F is in/out... and gallons is the last important spot. Col F would be add/subtract based on the in/out drop down used to updated Col K. the problem is i don't know how to create the dynamic link that when the chemical in col C is selected, it will update the correct total in col K. I tried sumif, vlookup, and a few others, but i am by far not a programer and have no idea how to make the statements.

Any ideas guys/girls??? I def need to get this figured out.



Chemical Tracker Dynamic.xlsxChemical Tracker Dynamic.xlsx

Not sure you're looking for a VBA solution, and this can likely be accomplished with some SUMIF's, but I suck at Excel formulas.

To use this code:

1. Open the VBA editor by pressing Alt+F11 in Excel
2. In the window that opens, you'll see a project explorer window on the left hand side
3. Find your spreadsheet, then listed under that, find the sheet you want this to run on (in your example, it would be Sheet1)
4. Double click that sheet
5. Paste the code in the white space to the right

Now the code will run automatically any time you enter a number into column G, assuming columns C and F are also filled out for that row.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And Range("F" & Target.Row) <> "" And _
Range("C" & Target.Row) <> "" Then
    Set c = Range("J:J").Find(Target.Offset(0, -4).Value)
    If Not c Is Nothing Then
        If Target.Offset(0, -1) = "In" Then
            c.Offset(0, 1).Value = c.Offset(0, 1).Value + Target.Value
        Else
            c.Offset(0, 1).Value = c.Offset(0, 1).Value - Target.Value
        End If
    End If
End If
End Sub