Updating inventory based on inserted INPUT or OUTPUT quantity

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.

AttachmentSize
Inventory.xlsx36.25 KB

I've attached a file to a

I've attached a file to a seperate thread - Response to updating inventory.