In this video I am going to show you how to track your inventory with barcodes in excel.
I have a workbook with four sheets here let us look at the inventory sheet
On the inventory sheet I have a list of the six product this company sells.
you will notice that I have a zero qty for everything.
Along with the barcode the have category, descripton along with cost,
our scan sheet is also our incoming sheet
so let us go there and buy some items
On this sheet the first thing I will enter is the quantity.
So let us enter 10, and the barcode is 2222 and we will press enter.
then on this sheet you will see what you have purchased and the date and time when this
happened.
so now let us go to the inventory sheet
you will notice that the Current qty is 10 and purchased in 2023 is 10
lets to back to our scan sheet
to enter value that have been sold, we use negative numbers
so lets enter -3 in the quanity and 2222 in the barcode and press enter
there is no change on your incoming sheet but if we go to the outgoing sheet you will
see that the product barcode, description,qty and the date and time that is was sold is
also listed,
if we go back to the inventory sheet on this item you can see that the current
qty is reduced by 3, the sold qty is 3
now let us look at the code.
first for this to run automatically you have code on the scan sheet
to view it you right click the sheet tab and select view code
There you can see the code.
it is basically saying that if c2 changes then call the program receive
so now let us look at the program receive
I have declared a few values at the top and then I say where the barcode and qty are located
on sheet3
The first thing I figure out is if there is a barcode or not
Then we search for the barcode on the inventory sheet.
It will send an error message if not found.
then we will determine which row has the barcode
if the qty is greater or equal to 1 then add the qty to the correct columns
next you are going to copy the description information
then you will paste it on the lastrow of the scan sheet
then you are going to enter the barcode and qty on the scan sheet
next you enter the date and time when this happened
if the qty is less than 1 -selling then add the qty to the correct columns
and then you will copy the description information next you will paste in on the lastrow of the
outgoing sheet then you are going to enter the barcode and
qty on the outgoing sheet next you enter the date and time when this
happened
go to the bottom of the code we will turn off the marching ants
next we will clear the cells on the scan sheet then we will select the qty cell on the scan
sheet
I will of course include the code in the description of the video
please subscribe