Scan barcode

[Gideondb]

I have 20 workers , each with a barcode.I need the active block to be A1 on a specific sheet . So if Paul scans his barcode i need the active block to go to his sheet, and if john scans his barcode i need the active block to go to John s sheet A1 ?

Can it be done ?

Thank you.

 

[Jeffrey Mahoney]

Yes,

You will always have to start with your active sheet and the cell where the barcode is going to reside be active. Create a change event for the barcode cell. Create a lookup table with all your employees names, corresponding barcodes, and sheet names. Have the Change event SUB find the sheet name from the table and WAH LA!

Jeff

 

[Gideondb]

Thank you for your responce : I dont know how to do this " Create a change event for the barcode cell. Create a lookup table with all your employees names, corresponding barcodes, and sheet names. Have the Change event SUB find the sheet name from the table" Can you help please. Thank you

 

[Jeffrey Mahoney]

My first post was to give you an indication that your request was not going to be answered with a simple post. Yes, I can help, but so can a lot of people here.

First thing you need to do is create your employee list. I suggest on a sheet of its own. Nothing else can be below the table. You need 3 or more columns:
* Name (exactly how it's going to be used throughout the workbook)
* Barcode number
* Sheet Name

For ease of use, you should create a named range for this table. I will help you create a dynamic named range. If you don't know how to create a named range, it is your duty to look that up using this site or google. Let's say you created the Employee table on a sheet called Employees starting in Cell A1. Where Cell A1 has the header "Name" in it. Cell B1 = Barcode. Cell C1 = Sheet Name. All the data below that. Create a new Named Range called "Employees_tbl" and use this formula: =OFFSET(Employees!$A$1,1,0,MATCH("zzzzzzzzzzzzzz",Employees!$A:$A)-ROW(Employees!$A$1),3)

Once you're done with that, get back to me.

Jeff

 

[Gideondb]

Thank you ill try.

 

[Gideondb]

Hello Jeff , Where should i do this ?Create a new Named Range called "Employees_tbl" and use this formula: =OFFSET(Employees!$A$1,1,0,MATCH("zzzzzzzzzzzzzz",Employees!$A:$A)-ROW(Employees!$A$1),3)
on a new sheet or in the block where i want the scanner to go to ? e.g. A1 Donnie ( sheet) ?
and the "zzzzzzzzzzzzz" What do i put in there or do i leave it as is ?

A named range has been created sheet Employees and i called it Employees,scope =Employees, range =Employees!$A$2:$A$19

 

[Gideondb]

In the new Range "Employees_tbl" i get a error on this part "$A$1,1,0,MATCH" : the error says i must type in a apostrophe (') first . I use axel 2013, done that still error

 

[Gideondb]

Sorry got it now , tried the formula gives me a error .

Hello Jeff , Where should i do this ?Create a new Named Range called "Employees_tbl" and use this formula: =OFFSET(Employees!$A$1,1,0,MATCH("zzzzzzzzzzzzzz",Employees!$A:$A)-ROW(Employees!$A$1),3)
on a new sheet or in the block where i want the scanner to go to ? e.g. A1 Donnie ( sheet) ?
and the "zzzzzzzzzzzzz" What do i put in there or do i leave it as is ?

A named range has been created sheet Employees and i called it Employees,scope =Employees, range =Employees!$A$2:$A$19

 

[Jeffrey Mahoney]

So... You copied or typed in your employee data onto a new sheet called Employees starting in cell A1, correct? Where A1 has the header called Name, or something like that, and the data is below?

You called the dynamic named range Employees_tbl? The formula for the named range looks exactly like the one I posted?

The named range shouldn't be the same name as the sheet name. I think Excel allows this, but that can get confusing. Did you give up on the dynamic formula I gave you and just hard code it to $A$2:$A$19. That won't work, the range needs to encompass the whole table.

When you get the dynamic name range to work. lets start working on the other parts. I'll be asking what your Employee sheet formats look like. I'll be asking what you want to do with the barcode information as it comes in. We'll be creating macros to recognize the barcode and get the data copied.

Jeff

 

[Gideondb]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Barcode[/TD]
[TD]Sheetname[/TD]
[/TR]
[TR]
[TD]Andries[/TD]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl63, width: 193"]Andries( a barcode font)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Andries[/TD]
[/TR]
[TR]
[TD]Ashley[/TD]
[TD]Ashley ( a barcode font)[/TD]
[TD]Ashley[/TD]
[/TR]
</tbody>[/TABLE]
I Typed in e.g. employee data onto a new sheet called Employees starting in cell A2 "andries" the A1 "Name" B1 " Barcode" and C1 "Sheetname" with the data under that in each colum (=Employees!$A$2:$A$19) table from blok A2 to A19 . My employees on this table goes to 19 employees , hence the "$A$2:$A$19" .
I called the dynamic named range Employees_tbl ( had to edit it , it was name) ( =Employees!$A$2:$A$19 ) table from blok A2 to C19.
Get the error :

Solved the error : I replaced all your " , " with "; " e.g. "=OFFSET(Employees!$A$1;1;0;MATCH("zzzzzzzzzzzzzz";Employees!$A:$A)-ROW(Employees!$A$1);3)"
and excell excepted the code , had that before on a sheet a recall working now.So the Dynamic sheet is done.
Thank you.We can continue !