Counting Inventory with Barcode Scanner

[RapidFireGT]

Windows 7
Excel 2007
Wasp WWS800 Wireless Barcode Scanner

First off, I'm not even sure if this is possible.

I've searched through 12 pages of "barcode" results and didn't find much, so I'll ask in this thread. Now, what I am about to ask it most likely too heavy for a complete answer, so even if you can just point me in the right direction, I'd appreciate it greatly.

What I will be doing
We are a wholesaler distributor and send out 2-4 pallets every day on each route, and we have about 5 routes everyday. At the end of each day before pallets are loaded onto the trucks, we count the product on the pallets to make sure the orders have been filled accurately. We would like to use a barcode scanner to scan each product and then populate a list of what products and what quantities are on the pallets.

What I would like Excel to do
I would like to set up a template with 3 columns: barcode, product name, and quantity. I would like to scan an item, the barcode scanner then inputs the barcode number into the "Barcode" column, Excel then cross references this number to match it with its corresponding product name which would then be inserted into the "Product Name" column, and then Excel would increase the quantity by 1 in the "Quantity" column.

Basically, I want insert the product number into excel, which then matches it with a product name and starts counting the number of times I scan that particular item.

Here is a sample spreadsheet of what I imagine such spreadsheet would appear as:

Again, I'm not even sure if this is possible in Excel, and I assume it is too much work for you guys just to spit out the formulas to me to accomplish this. With that said, if you can even point me in the right direction to achieving this, I would appreciate it.

Thanks guys!

 

[brucef2112]

Depending on the barcodes you scan the given code posted previously could give bad inventory results.

The "LookAt:=xlPart" should be changed to "LookAt:=xlWhole"

With "xlPart" a bar code #5678 could be the (incorrect) 'found' bar code if the user actually scanned bar code #678. Basically any small value bar code that is also part of a longer bar code string could return incorrect results. This will be avoided by changing it to "xlWhole". ie lt will look for the whole value. This way the two example numbers will return the correct results. This would most likely show up if you have a mix of products imported from other countries with various types of bar codes and or if you have your own internal store bar codes that you created.

Also the code missed when a non existent number is scanned. it adds it to the inventory list but does not increment the inventory count to 1. only after the second time the item is scanned does it show the count as 1. So new bar code counts are off by 1. And of course the description is left blank.

I've modified to handle the situation where you may find an item in the store that isn't in the database. it adds the bar code and prompts you for a description and also a price which are both inserted into the inventory worksheet. Description is in column B. The price is added to column D.
This way you know the line items with the prices are the ones that will need to be added back into the store's inventory system/POS. Rare to happen, but it does.

Also, the code above just kept scrolling to the right with each scan. yuck!
I changed it so after scanning a bar code the focus is returned to F1 cell to allow you to keep scanning.
It also scrolls the screen to the current item's line when scanned. Nice to see the item you incremented and or able to manually update count. ie you can scan a single piece and instead of repeated scanning the rest you can just go to the count column and manually add the rest. ie if there is a case of 50 items just manually add the 50 to the current number and type it in. Then click on the f1 cell and start scanning again.
For the above feature to work you'll need to vertically pane split the worksheet between columns E and F. and then also horizontally between row 1 and 2. And then enable Freeze Pane. under the menu item Window > Freeze Pane. Once you get past the visible rows on your screen you'll see how much nicer this is when the current row scrolls to top of the screen.

Here is a link to a sample spreadsheet. (using excel 2002)
TakeInventorySample.xls

If you prefer, here's the code part by its self. You'll then have to set up the Freeze Pane on your own.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Item As String
Dim strDscrpt As String
Dim strPrice As String
Dim SearchRange As Range
Dim rFound As Range

'Don't run the macro if:
'Target is not a single cell:
If Target.Cells.Count > 1 Then Exit Sub
'or Target belongs to the A1.CurrentRegion:
If Not Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub

'Avoid the endless loop:
Application.EnableEvents = False

'Looks for matches from here first:
Set SearchRange = Range("A1:A" & Range("A1").CurrentRegion.Rows.Count)
    
Item = Target.Value

'Clears the Target:
Target.Value = ""

If Application.WorksheetFunction.CountIf(SearchRange, Item) > 0 Then
'There's a match already:
    Set rFound = Columns(1).Find(What:=Item, After:=Cells(1, 1) _
            , LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows _
            , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'Adds one to the Quantity:
        rFound.Offset(0, 2).Value = rFound.Offset(0, 2).Value + 1
        rFound.Activate
        Application.Goto ActiveCell, True
Else

'Writes the value for the Barcode-list:
Range("A" & SearchRange.Rows.Count + 1).Value = Item

'Looks for the match from sheet "Inventory" column A
    With Sheets("Inventory")
        Set rFound = .Columns(1).Find(What:=Item, After:=.Cells(1, 1) _
                , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows _
                , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
        On Error GoTo 0
    
            If Not rFound Is Nothing Then
'Writes the Product Name and adds 1 to the Quantity column:
                Range("B" & SearchRange.Rows.Count + 1).Value = rFound.Offset(0, 1).Value
                Range("C" & SearchRange.Rows.Count + 1).Value = 1
                
                'Scroll worksheet to the current item in case user want to see it or work with it.
                Range("C" & SearchRange.Rows.Count + 1).Activate
                Application.Goto ActiveCell, True
                
            Else
                'The Product isn't in the Inventory sheet.
                'sound beep to alert user to add description and price.
                Range("C" & SearchRange.Rows.Count + 1).Value = 1
                
                Beep
                For i = 1 To 15000000
                    'just killing one second or so, so we can get a second 'beep' in.
                    Next i
                Beep
                
                ' IF user is quick to scan another barcode then the description would be entered as that barcode.
                ' So we avoid this by checking if the discription entered is a number or if its blank and loop
                ' until we get the user to enter some text. If a description is actually a number then user should procede the number
                ' with a single quote mark. This ensures that the user really want to enter a number as a description.
                
                Do
                    strDscrpt = InputBox("Enter Description for Barcode: " & Range("A" & SearchRange.Rows.Count + 1).Value & vbCr & vbLf & "(24 characters max)", "Item Not found in Inventory List")
                Loop While IsNumeric(strDscrpt) Or (Len(Trim(strDscrpt)) = 0)
                
                Range("B" & SearchRange.Rows.Count + 1).Value = strDscrpt
                
                Beep
                Do
                    strPrice = InputBox("Now enter the regular PRICE for: " & UCase(strDscrpt) & vbCr & vbLf & "(With decimal point. example: 12.99)", "Price for Barcode: " & Range("A" & SearchRange.Rows.Count + 1).Value)
                Loop While Not IsNumeric(strPrice)
                
                Range("D" & SearchRange.Rows.Count + 1).Value = Val(strPrice)
                
                'Scroll worksheet to the current item in case user want to see it or work with it.
                Range("C" & SearchRange.Rows.Count + 1).Activate
                Application.Goto ActiveCell, True
            End If
    End With
End If

Range("F1").Value = "Scan Barcode Here"
Range("F1").Select

'Enable the Events again:
Application.EnableEvents = True


End Sub

 

[nikzomer]

how to take only negative contain row to another sheet?

 

[excel123456789]

Depending on the barcodes you scan the given code posted previously could give bad inventory results.

The "LookAt:=xlPart" should be changed to "LookAt:=xlWhole"

With "xlPart" a bar code #5678 could be the (incorrect) 'found' bar code if the user actually scanned bar code #678. Basically any small value bar code that is also part of a longer bar code string could return incorrect results. This will be avoided by changing it to "xlWhole". ie lt will look for the whole value. This way the two example numbers will return the correct results. This would most likely show up if you have a mix of products imported from other countries with various types of bar codes and or if you have your own internal store bar codes that you created.

Also the code missed when a non existent number is scanned. it adds it to the inventory list but does not increment the inventory count to 1. only after the second time the item is scanned does it show the count as 1. So new bar code counts are off by 1. And of course the description is left blank.

I've modified to handle the situation where you may find an item in the store that isn't in the database. it adds the bar code and prompts you for a description and also a price which are both inserted into the inventory worksheet. Description is in column B. The price is added to column D.
This way you know the line items with the prices are the ones that will need to be added back into the store's inventory system/POS. Rare to happen, but it does.

Also, the code above just kept scrolling to the right with each scan. yuck!
I changed it so after scanning a bar code the focus is returned to F1 cell to allow you to keep scanning.
It also scrolls the screen to the current item's line when scanned. Nice to see the item you incremented and or able to manually update count. ie you can scan a single piece and instead of repeated scanning the rest you can just go to the count column and manually add the rest. ie if there is a case of 50 items just manually add the 50 to the current number and type it in. Then click on the f1 cell and start scanning again.
For the above feature to work you'll need to vertically pane split the worksheet between columns E and F. and then also horizontally between row 1 and 2. And then enable Freeze Pane. under the menu item Window > Freeze Pane. Once you get past the visible rows on your screen you'll see how much nicer this is when the current row scrolls to top of the screen.

Here is a link to a sample spreadsheet. (using excel 2002)
TakeInventorySample.xls

If you prefer, here's the code part by its self. You'll then have to set up the Freeze Pane on your own.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Item As String
Dim strDscrpt As String
Dim strPrice As String
Dim SearchRange As Range
Dim rFound As Range

'Don't run the macro if:
'Target is not a single cell:
If Target.Cells.Count > 1 Then Exit Sub
'or Target belongs to the A1.CurrentRegion:
If Not Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub

'Avoid the endless loop:
Application.EnableEvents = False

'Looks for matches from here first:
Set SearchRange = Range("A1:A" & Range("A1").CurrentRegion.Rows.Count)
    
Item = Target.Value

'Clears the Target:
Target.Value = ""

If Application.WorksheetFunction.CountIf(SearchRange, Item) > 0 Then
'There's a match already:
    Set rFound = Columns(1).Find(What:=Item, After:=Cells(1, 1) _
            , LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows _
            , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'Adds one to the Quantity:
        rFound.Offset(0, 2).Value = rFound.Offset(0, 2).Value + 1
        rFound.Activate
        Application.Goto ActiveCell, True
Else

'Writes the value for the Barcode-list:
Range("A" & SearchRange.Rows.Count + 1).Value = Item

'Looks for the match from sheet "Inventory" column A
    With Sheets("Inventory")
        Set rFound = .Columns(1).Find(What:=Item, After:=.Cells(1, 1) _
                , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows _
                , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
        On Error GoTo 0
    
            If Not rFound Is Nothing Then
'Writes the Product Name and adds 1 to the Quantity column:
                Range("B" & SearchRange.Rows.Count + 1).Value = rFound.Offset(0, 1).Value
                Range("C" & SearchRange.Rows.Count + 1).Value = 1
                
                'Scroll worksheet to the current item in case user want to see it or work with it.
                Range("C" & SearchRange.Rows.Count + 1).Activate
                Application.Goto ActiveCell, True
                
            Else
                'The Product isn't in the Inventory sheet.
                'sound beep to alert user to add description and price.
                Range("C" & SearchRange.Rows.Count + 1).Value = 1
                
                Beep
                For i = 1 To 15000000
                    'just killing one second or so, so we can get a second 'beep' in.
                    Next i
                Beep
                
                ' IF user is quick to scan another barcode then the description would be entered as that barcode.
                ' So we avoid this by checking if the discription entered is a number or if its blank and loop
                ' until we get the user to enter some text. If a description is actually a number then user should procede the number
                ' with a single quote mark. This ensures that the user really want to enter a number as a description.
                
                Do
                    strDscrpt = InputBox("Enter Description for Barcode: " & Range("A" & SearchRange.Rows.Count + 1).Value & vbCr & vbLf & "(24 characters max)", "Item Not found in Inventory List")
                Loop While IsNumeric(strDscrpt) Or (Len(Trim(strDscrpt)) = 0)
                
                Range("B" & SearchRange.Rows.Count + 1).Value = strDscrpt
                
                Beep
                Do
                    strPrice = InputBox("Now enter the regular PRICE for: " & UCase(strDscrpt) & vbCr & vbLf & "(With decimal point. example: 12.99)", "Price for Barcode: " & Range("A" & SearchRange.Rows.Count + 1).Value)
                Loop While Not IsNumeric(strPrice)
                
                Range("D" & SearchRange.Rows.Count + 1).Value = Val(strPrice)
                
                'Scroll worksheet to the current item in case user want to see it or work with it.
                Range("C" & SearchRange.Rows.Count + 1).Activate
                Application.Goto ActiveCell, True
            End If
    End With
End If

Range("F1").Value = "Scan Barcode Here"
Range("F1").Select

'Enable the Events again:
Application.EnableEvents = True


End Sub

This is a really great app, works very well. I have a question though, and i cannot figure out how to do it.

When the "Enter Product Description" dialog box appears, and you enter the information, the data is written to the "Inv Taken" sheet. However, It would be helpful for the barcode and product description to be written to the "Inventory" sheet as well - which will help build up the Inventory database.

How can i accomplish this? The data would need to be written to the Inventory sheet column A and B, within a row that does not have any data yet (in your spreadsheet example, row 11).

Any ideas?

 

[excel123456789]

bump for ideas?

 

[Misca]

This piece of code writes the Item and the description to the columns A & B on the sheet "Inventory":

With Sheets(“Inventory”)
'Finds the first empty cell from the bottom up:
.cells(.rows.count,1).end(xlup).offset(1).resize(,2).value=array(Item,strDscrpt)
end with

Just add the lines to the original code after the line

Range("B" & SearchRange.Rows.Count + 1).Value = strDscrpt

 

[excel123456789]

This piece of code writes the Item and the description to the columns A & B on the sheet "Inventory":

With Sheets(“Inventory”)
'Finds the first empty cell from the bottom up:
.cells(.rows.count,1).end(xlup).offset(1).resize(,2).value=array(Item,strDscrpt)
end with

Just add the lines to the original code after the line

Range("B" & SearchRange.Rows.Count + 1).Value = strDscrpt

Thank you very much, works great!

 

[sanjuda]

The codes on this board dosent work anymore. Pls help with a working excel file or recheck the codes..... The codes looks promising however it dosent work anymore please help ???

Depending on the barcodes you scan the given code posted previously could give bad inventory results.

The "LookAt:=xlPart" should be changed to "LookAt:=xlWhole"

With "xlPart" a bar code #5678 could be the (incorrect) 'found' bar code if the user actually scanned bar code #678. Basically any small value bar code that is also part of a longer bar code string could return incorrect results. This will be avoided by changing it to "xlWhole". ie lt will look for the whole value. This way the two example numbers will return the correct results. This would most likely show up if you have a mix of products imported from other countries with various types of bar codes and or if you have your own internal store bar codes that you created.

Also the code missed when a non existent number is scanned. it adds it to the inventory list but does not increment the inventory count to 1. only after the second time the item is scanned does it show the count as 1. So new bar code counts are off by 1. And of course the description is left blank.

I've modified to handle the situation where you may find an item in the store that isn't in the database. it adds the bar code and prompts you for a description and also a price which are both inserted into the inventory worksheet. Description is in column B. The price is added to column D.
This way you know the line items with the prices are the ones that will need to be added back into the store's inventory system/POS. Rare to happen, but it does.

Also, the code above just kept scrolling to the right with each scan. yuck!
I changed it so after scanning a bar code the focus is returned to F1 cell to allow you to keep scanning.
It also scrolls the screen to the current item's line when scanned. Nice to see the item you incremented and or able to manually update count. ie you can scan a single piece and instead of repeated scanning the rest you can just go to the count column and manually add the rest. ie if there is a case of 50 items just manually add the 50 to the current number and type it in. Then click on the f1 cell and start scanning again.
For the above feature to work you'll need to vertically pane split the worksheet between columns E and F. and then also horizontally between row 1 and 2. And then enable Freeze Pane. under the menu item Window > Freeze Pane. Once you get past the visible rows on your screen you'll see how much nicer this is when the current row scrolls to top of the screen.

Here is a link to a sample spreadsheet. (using excel 2002)
TakeInventorySample.xls

If you prefer, here's the code part by its self. You'll then have to set up the Freeze Pane on your own.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Item As String
Dim strDscrpt As String
Dim strPrice As String
Dim SearchRange As Range
Dim rFound As Range

'Don't run the macro if:
'Target is not a single cell:
If Target.Cells.Count > 1 Then Exit Sub
'or Target belongs to the A1.CurrentRegion:
If Not Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub

'Avoid the endless loop:
Application.EnableEvents = False

'Looks for matches from here first:
Set SearchRange = Range("A1:A" & Range("A1").CurrentRegion.Rows.Count)
    
Item = Target.Value

'Clears the Target:
Target.Value = ""

If Application.WorksheetFunction.CountIf(SearchRange, Item) > 0 Then
'There's a match already:
    Set rFound = Columns(1).Find(What:=Item, After:=Cells(1, 1) _
            , LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows _
            , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'Adds one to the Quantity:
        rFound.Offset(0, 2).Value = rFound.Offset(0, 2).Value + 1
        rFound.Activate
        Application.Goto ActiveCell, True
Else

'Writes the value for the Barcode-list:
Range("A" & SearchRange.Rows.Count + 1).Value = Item

'Looks for the match from sheet "Inventory" column A
    With Sheets("Inventory")
        Set rFound = .Columns(1).Find(What:=Item, After:=.Cells(1, 1) _
                , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows _
                , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
        On Error GoTo 0
    
            If Not rFound Is Nothing Then
'Writes the Product Name and adds 1 to the Quantity column:
                Range("B" & SearchRange.Rows.Count + 1).Value = rFound.Offset(0, 1).Value
                Range("C" & SearchRange.Rows.Count + 1).Value = 1
                
                'Scroll worksheet to the current item in case user want to see it or work with it.
                Range("C" & SearchRange.Rows.Count + 1).Activate
                Application.Goto ActiveCell, True
                
            Else
                'The Product isn't in the Inventory sheet.
                'sound beep to alert user to add description and price.
                Range("C" & SearchRange.Rows.Count + 1).Value = 1
                
                Beep
                For i = 1 To 15000000
                    'just killing one second or so, so we can get a second 'beep' in.
                    Next i
                Beep
                
                ' IF user is quick to scan another barcode then the description would be entered as that barcode.
                ' So we avoid this by checking if the discription entered is a number or if its blank and loop
                ' until we get the user to enter some text. If a description is actually a number then user should procede the number
                ' with a single quote mark. This ensures that the user really want to enter a number as a description.
                
                Do
                    strDscrpt = InputBox("Enter Description for Barcode: " & Range("A" & SearchRange.Rows.Count + 1).Value & vbCr & vbLf & "(24 characters max)", "Item Not found in Inventory List")
                Loop While IsNumeric(strDscrpt) Or (Len(Trim(strDscrpt)) = 0)
                
                Range("B" & SearchRange.Rows.Count + 1).Value = strDscrpt
                
                Beep
                Do
                    strPrice = InputBox("Now enter the regular PRICE for: " & UCase(strDscrpt) & vbCr & vbLf & "(With decimal point. example: 12.99)", "Price for Barcode: " & Range("A" & SearchRange.Rows.Count + 1).Value)
                Loop While Not IsNumeric(strPrice)
                
                Range("D" & SearchRange.Rows.Count + 1).Value = Val(strPrice)
                
                'Scroll worksheet to the current item in case user want to see it or work with it.
                Range("C" & SearchRange.Rows.Count + 1).Activate
                Application.Goto ActiveCell, True
            End If
    End With
End If

Range("F1").Value = "Scan Barcode Here"
Range("F1").Select

'Enable the Events again:
Application.EnableEvents = True


End Sub

 

[sanjuda]

If the same Barcode/EAN or sku id is scanned twice then it dosent adds up the quntity of that particular barcode/EAN or sku id and shows up an error.

 

[Bill M]

Could someone help me massage this macro to have it copy the price and the next 2 columns from the inventory sheet. The macro does exactly what I need except the range needs to be extended.
Thanks

 

[brucef2112]

Could someone help me massage this macro to have it copy the price and the next 2 columns from the inventory sheet. The macro does exactly what I need except the range needs to be extended.
Thanks

Wow, I was Googling something and a link to this came up!?!?! So I had to check. Crazy it started many years ago. Although I see Bill's post is 2 months ago, relative to the years and years gone by, I figured there is some interest and I'd bump this and try to help him and others.
First to the issue of it not working. I went back to my original post and clicked the link (it's still there) and download it. It works just fine for me. Please note if you just copied and pasted the code, it is important that setting up the Freeze Pain is just as important for to this work. Lots of other things could go wrong. Better to just D/L the spreadsheet.
Here is a link to a sample spreadsheet. (using excel 2002)
TakeInventorySample.xls

Now for the rest.
As an inventory tool, it does that quite well. There are many purposes that everyone can use the initial inventory for. Modifying this for each persons needs would be very time consuming and in the end there would still be 10's of people who want yet something else.

The fact that you have 2 more columns in your inventory list is just one of many variations the 10's of other people do with their inventory data. My real data has lots and lots of columns. However to take inventory the stock#, description, inventory count and price are enough information to have the beginnings of a successful audit (You scan, verify it the correct item by description, and give a quick see that the price is correct and take note of the count. hoping it cross checks with the stuff purchased).

Here is what I do.
Once inventory is done, I copy the scanned data from the Inv Taken worksheet and paste it into a new spreadsheet (Hint: right-click on the sheet's tab and from the popup menu > 'Move Copy...' > Select New Worksheet from drop-down box > Check the box "Create Copy" > OK)

Now, with both worksheets in another workbook you can use some of Excel's functions and features to massage your data into something useful for your specific needs.

Note again you are doing this in another workbook, and not the inventory one used to take the inventory.
In your case, needing 2 more columns of data next to the other four. This can be quickly done by using a vLookup formula.
For example, You have something interesting in column H and column M, in the Inventory worksheet that you want to see to the right of the price column in Inv Taken worksheet.

Getting stuff from column H over to Inv Taken sheet
Go to the Inv Taken Sheet and delete column F (the one with data entry cell). Select cell E2 ( assuming you have column headers) and enter the following vLookup formula.
=VLOOKUP($A2, Inventory!$A$2:$R$100,8, False) Note: your actual range needs to match the one in your table. IT MUST BE THE ENTIRE RANGE OF ALL YOUR DATA. ie A2....to whatever the last row and column ALL your data is in!!!!!

Start of VERY IMPORTANT stuff!
Notice the placement of $ in the cell address of the First Argument $A2 must be there.
Notice the placement of $'s in the cell address of the Second Argument Inventory!$A$2:$R$100 . Note there are 2 $ for the cell address. ....$A$2..
Doing it this way will make copying the formula to the rest of the cells EZ and ensures they'll have the proper formula in them.

so:
First Argument = cell info you want to use to find a match in the other worksheet. The barcode# or stock number.
Second Argument = cell range you want to look in to find a match, and return a cell value somewhere in the same row.
In this case, its on the Inventory worksheet, and ALL the data, I repeat, ALL the data is in the range of A2 thru R100.
So it needs to look like this: Inventory!$A$2:$R$100. Again, note the $$$$$$ positions.
Third Argument = The column who's data you want to bring over to the Inv Taken sheet. In this case I want what is in my column M. So my example is 8.
(go ahead and count your ABC's, I'll wait.....)
Forth Argument = Don't worry, just make sure it is always FALSE.
End of VERY IMPORTANT stuff!

IF YOU ENTERED YOUR FORMULA CORRECTLY, Here is what happens.
This formula will get the stock# from Inv Taken, cell A2, and look through column A on the Inventory sheet.
If it finds a match, it will go over to column H and return this value found back to the Inv taken sheet in cell E2. (the one with the formula).
If it doesn't find a match it will return #N/A.

Getting stuff from column M over to Inv Taken sheet
Now lets set up column F on the Inv Taken sheet. To get what's in column M from the Inventory sheet, this is easy as cut/paste.
Copy cell E2 and past into F2.
Take a look at my formula after pasting. =VLOOKUP($A2, Inventory!$A$2:$R$100,8, False)
Notice it didn't change AT all. This is good and happened because of the clever placement of $$$'s!
But to get the data from column M, all I need to change in the formula of cell F2 is to change the 8 to 13. (go ahead and count your ABC's from A>M, I'll wait.....)
This will now do the same as the previous except now it is returning what is in column M on the Inventory sheet.

To fill the formulas down both columns for all rows in the entire Inv Taken sheet, select both cells containing the formulas and copy, then past into the remaining range columns E and F of the Inv Taken sheet.
So if row 500 is the last row in your inv Taken then you would copy cells E2 and F2 down to row 500.
After pasting, spot check formulas in the copied cells. The RANGE reference to the Inventory sheet must be the same in ALL cell formulas.
In my example Inventory!$A$2:$R$100 must be the same for every cell in columns E and F. This is the range we are searching in, so it must be the same in all rows.
What should have changed, is the first argument of $A2. As you copy it down the row value should change to match the row that the formula is in.

Q: If the formula is correct why would I get an #N/A?
A: Remember during your inventory count, if you had an item not in the stock table it prompted you for a desc, and price? This would be just cause for the #N/A. ie It isn't in your stock table yet! (Did you remember to add these new items to the stock table?)

10's of other reasons exist, If this is your first round with vlookup, it very well could be operator error.

For myself, once the vlookup is complete, I hard copy the values into the cells. In this way I can move, copy, the Inv Taken sheet without having to maintain the link to the other sheet.
Select all the cells with the vlookup formulas (columns E and F).
Right click in the selection and choose copy.
With the same selection there, I then again right click and choose Past Special and then choose VALUES. This does exactly that. It will copy the results of the vlookup and paste them back with the VALUES instead of the formula.

You now have free will to copy, share, move, whatever, with the new InvTaken with added data from the Inventory sheet without worrying about keeping the vlookup addressing correct.

OMG I have way too much time...