Excel USB Scanner Useage

[adrobert]

I'm trying to use a barcode scanner in Excel to help with our inventory. Using the scanner, the barcode information is placed in cell a1 of sheet 1. What I would like to happen is that when the information is scanned in cell a1, it does a lookup on sheet3 where I have the complete inventory list and locate. It will then find the corresponding part number and put the description in cell a2. Tried using vlookup but no luck as I'm quite new to all this. Looking for a little help and guidance

 

[BarCodeWiz]

You can use VLOOKUP like this:

=VLOOKUP(A1,Sheet3!A1:B100,2)

This searches the first 100 cells of column A on Sheet3 for the barcode value. If found, the information from Column B (description) is returned (that's the Column B = 2 in the formula).

Make sure the barcode column is sorted. Also, if you use column headings, or for some other reason the barcodes don't start on row 1, you can change the range to something like Sheet3!A2:B100

Hope this helps.

 

[adrobert]

Thank you very much for the reply and I can't tell you how much it's appreciated. Didn't get to try it until Sunday due to family commitments. How can I copy and paste this to the lower cells without each cell location in the formula automatically increasing, i.e
=VLOOKUP(A1,Sheet2!A1:B100,2) and in the pasted cell, =VLOOKUP(A2,Sheet2!A2:B100,2).

The first cell location is of course ok as this is where the info would be scanned, but with each iteration, if a barcode is scanned that's stored prior to the search pattern A14:B114, then it wouldn't be found. Also, being that I will be using it for inventory, I'm going to need to figure out how to get it to count the entries.

Again, thanks for the help and like I said, I am new to all this!

 

[BarCodeWiz]

Adrobert,
You can use the $ sign before the cell address to keep it from changing:

=VLOOKUP(A1,Sheet3!$A$1:$B$100,2)

Here, if you copy the formula to other cells, the first argument will increase (from A1 to B1,C1 and so on) but the rest will stay the same. I think this is what you're after.

 

[adrobert]

U Da Man... or Woman! Worked a treat!!! Now to get this thing to count

 

[BarCodeWiz]

lol... I'm most definitely a man.

This will count all non-empty values in column A:

=COUNT(A:A)

 

[adrobert]

=Count parameter works great but now I'm trying to figure out how to have the first entry of let's say part number 119200 entered in cell A1 and if there are 25 peices, it only displays the part number once in cell A1 but maybe creates a hidden worksheet to count the remaining 24.

The only thing that you see on the initial sheet is the entry in cell A1 of 119200 , what it is in the next cell and finally the count of 25.

For the next cell, A2, another part number, 119201 is entered and I would guess, the hidden worksheet is cleared and the process starts all over again. I'm as lost as a big lost thing and all help is appreciated.

 

[RichardMGreen]

If your part number on Sheet3 appear once per item (i.e. to use your example of part number 119200, it shows 25 times) then the formula
=COUNTIF(Sheet3!$A:$B,A1)
would give you the number of times each part number appears.

 

[rs2k]

I would recomend using the 'not_exact_match' field after the 'index_number' field as well

From

=VLOOKUP(A1,Sheet3!$A$1:$B$100,2)

to

=VLOOKUP(A1,Sheet3!$A$1:$B$100,2,0)

or you could end up with some spurious results,

HTH
Colin