Minus or add items quantity every time new inventory registered IN or OUT

Hi guys..

I need ur help here.

i want to develop an inventory workbook that can automatically update the balance quantity for every input or output items.

Every item has their own code. what i need is, when i input :

A) item code in E6 (sheet1)
B) IN or OUT process in E8 ( sheet1)
D) quantity in E10 (sheet1)

then the formula can automatically lookup the necassary item from column B (sheet2) and update the respective inventory in column H (sheet2) based on values in step B and C above. These 2 operations means.

if E8 = IN

updated inventory in column H(sheet2) = previous inventory in column H (sheet2) + E10(sheet1)


if E8 = OUT

updated inventory in column H(sheet2) = previous inventory in column H (sheet2) + E10(sheet1)


But if updated inventory in column H(sheet2) < 0 , then the operation become non valid and i need the macro to notify the user with "alert" message. Dont update the inventory for this case.


Sorry for the long question. I need this program so bad, but i dont have any basic about macro. im not even sure if regular excel function capale to do this.

Thanks in advance for helping really appreaciate your help.