On Apr 5, 6:54 pm, "Rodjk #613" wrote:
On Apr 4, 7:32 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:



I don't see any way to correlate the badge number scanned in with either of
the two items of information on the second sheet. To do this automatically
you need to have the badge number over in the second sheet also. Idealy the
setup would be (for the second sheet) 3 columns, with first column (A?)
holding the badge ID assigned, then next column could have employee name and
third column with their ID number. 2nd and 3rd columns could be other way
around, key is to have the badge number in the leftmost column of that
'table'.

Then back on the first sheet where the badge scanner is going to put the
scanned number into column A, you set up a VLOOKUP() formula in columns B and
C.

For this example we will say that you have 100 employees and their badge #s,
names and ID #s are in A1:C100 on the second sheet, with badge number in A,
name in B and ID # in C.

On first sheet in, assuming labels in row 1, data starting in row 2, put
this into B2
=IF(A20,VLOOKUP(A2,'Sheet2'!$A$1:$C$100,2,FALSE), "")
and in C2 put this
=IF(A20,VLOOKUP(A2,'Sheet2'!$A$1:$C$100,3,FALSE), "")

then fill the formulas on down the sheet. When an entry is made into column
A on that first sheet, you will either see the name and ID # for the matching
employee show up in B and C or you will see #N/A in those cells if the
scanned in badge number does not match an entry in the table on the second
sheet. We could hide the "#N/A" error - but in this case it serves as an
alert that either the scanner did not read the badge properly or that the
badge used is not assigned to someone in your employee list.

Thank you very much!
That worked just fine, and you are right about the badge ID.
I do have it in a column on spreadsheet 2 so it can match up.
My bad, but you got it anyway!

Ok, as usual when I try these things there is an unforeseen issue.
Lets say the badge ID is '12345678'
When the barcode is scanned, it has an Initiator key built in, which
means that while the badge ID is
12345678 it is actually read as 12345678*. (* implies a wild card,
could be any letter or symbol)

I am trying to find a simple way to remove that last digit, while
keeping the cells the same.
My goal is to have the number scanned in as '12345678*' and when I
push 'Enter' to move to the next cell down, have that last number
filtered out. Then the other cells auto fill with your code above.

Is there any way to accomplish this?
I got it to work, but all cells with the code now show #VALUE and I
cannot figure out how to get rid of that w/o screwing up the
formatting. This spreadsheet needs to be as 'idiot proof' as possible!

Thanks again,Rodjk#613



"Rodjk#613" wrote:
Hello,
Ok, I have a spreadsheet with two worksheets.
The second sheet will be employee names and ID numbers.
The first sheet will be set up to use a badge ID scanner.

The scanner will read the bar code on the badge, then input the badge
number to column A.
What I would like is a Macro of some sort that will fill in column B
and C with the ID Number and the Name of the employee. (The badge
number and the ID number are different.)

Is this possible?

Thanks
Rod

Ok, I got something that works.
I found this in another post:
I did a conditional formating and set A1 to "=iserror(a1)"
Then I set the format to paint the cells white.
So I lost the #VALUE error in all the unoccupied cells.

I hope this helps someone else.

Rodjk #613