How to auto update product stock when a sale happens ?

[psahani]

On sheet1 I have product lists along with all the details and unique barcode.

On sheet1 "barcode" column is "A" and "stock quantity" column is "E".
On sheet2 "barcode" column will be "A" for scanning the barcodes and available "stock quantity" column will be "B".


what I want is whenever I scan barcodes on column "A" on sheet2 the following things should happen...

1) column "B" should display the available stock quantity and column "C" value will be "1".

[TABLE="class: cms_table_outer_border, width: 500"]
<tbody>[TR]
[TD]barcode (A)[/TD]
[TD]stock quantity(B)[/TD]
[TD]sale quantity(C)[/TD]
[/TR]
[TR]
[TD]barcode1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]barcode2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]barcode3[/TD]
[TD]10[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]


2) My data contains few same barcodes with different stocks (including 0 stock) quantity (but product is same) on sheet1. So if I scan a barcode on sheet2 column "A" then column "B" should display the product quantity which is equal or grater than "1".

3) On cell "D2" I will have a button called "Update". And when I will click the "update" button my stocks on sheet1 will be updated calculating this (column B - column C)