Inventory and Weekly sales worksheet

Hi,
I'm very new to MS Excel. Never worked with MS Excel before. I picked up a bit about MS Excel 2013 off the internet and try to build Inventory and Weekly sales worksheet.
I used VLOOKUP function for finding products name, codes, price , level of stock and type of the products.
Now the PROBLEM is that every time I enter same product name , the level of stock is not subtracted. It remains the same.
For example, If today I sold an "ITEM 1" with level of stock 9, it automatically subtracted the level of stock from 9 to 8 . but the next day, i sold the same product with the same quantity, the level of stock will still appear as 9 . so it is not automatically calculate my level of stock column.

this might sound confusing how the way i explain, but if u get it, please help me.

Sincere Thanks

here it is.

you might notice different language in there.

Tranlated = Indonesia - English

Hari = Days
Kode Barang = Product Codes
Nama Barang = Product Names
Harga = Price
Kuantitas = Quantity
stock tersisa = remaining stock

maybe with pic you can understand better.
sorry for my poor english
ikrammmm.jpg

you can see that the same products are there. in the 'STOCK' column, it says 8 in the first column and the second one. now if you can see the quantity, it shows that I sold 6 of them and the below one shows that I sold 2 of them. NOW , if you see the "remaining stock" (stock tersisa)just before 'TYPE MOBIL' , it should be 0 in the remaining stock, because I sold 8 in total and there are only 8 available in the stock.

Sorry for asking it, but i'm really new to this , so where exactly should I enter the " =[@Stock]-SUMIF(B:B,[@[Kode Barang]],E:E) " ?

sorry again. didn't see it properly.

HUGE THANKS. appreciate it.

THANKS!

Hi,

Thanks a lot again for the help.

by that, are you saying I cannot repeat the product for more than 2 times ?

Sorry for asking too much, but this is how i want my spreadsheet should work.
I want to make it for a weekly sales spreadsheet. So I might be entering the same products with same product codes twice or more than twice in the different day.
newwww.jpg

PLEASE HELP ME ! .. it's playing up again.
Have no idea what's happening now.

please check the attached file.
and see a picture of it.

The products stock , remaining stock and cars type are not known in the columns. it should be automatically appear whenever I enter the product codes.
It seems that I cannot repeat the same product codes.

Would someone please help me with this ?

Much appreciated

excelll.jpg

to be fixed(help).xlsx THE SPREADSHEET IS HERE !!!

In the future, pay attention, read all the responses received. If you read the answer nr.11, you would not have had "problems".

ok . thank you indi.
That is so helpful.
Thanks for understanding that I am very new at this.

Thanks again.
much appreciated

Thank so much etaf !

Helped me a lot !!!