EXCEL Sheet and blocks

[Gideondb]

1. In excel 2013 , can you scan a barcode so that that barcode takes you to n spesific block A1 on a specific sheet. ?

2. In the first sheet i have in colum A serl. no.s , in colum C a bar code no.s and in colum D a Name.In another Sheet i scan items in RANDOM BARCODES , I want the name and serl. number to appear in the blocks next to it from sheet 1 ?

1 SHEET

[TABLE="width: 458"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[/TR]
[TR]
[TD]Serl. No[/TD]
[TD]Bar code[/TD]
[TD] Bar code no [/TD]
[TD] Item name[/TD]
[/TR]
[TR]
[TD]BWStock00001[/TD]
[TD][/TD]
[TD]6001599054393[/TD]
[TD] Diary[/TD]
[/TR]
[TR]
[TD]BWStock00002[/TD]
[TD][/TD]
[TD]6001015506017[/TD]
[TD] tin[/TD]
[/TR]
[TR]
[TD]BWStock00003[/TD]
[TD][/TD]
[TD]6001007316884[/TD]
[TD] Paper Towl[/TD]
[/TR]
[TR]
[TD]BWStock00004[/TD]
[TD][/TD]
[TD]6001007160937[/TD]
[TD] toilet paper[/TD]
[/TR]
[TR]
[TD]BWStock00005[/TD]
[TD][/TD]
[TD]6001087006033 [/TD]
[TD] HAMMER

[/TD]
[/TR]
</tbody>[/TABLE]
2 SHEET

[TABLE="width: 519"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD] A B[/TD]
[TD][/TD]
[TD] C[/TD]
[TD] D [/TD]
[TD] E[/TD]
[TD] F[/TD]
[TD] G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Barcode no[/TD]
[TD]Serl. no. [/TD]
[TD]Item Name[/TD]
[TD]Units[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[bobsan42]

if possible - move column C (Barcode) to be the first column on the Left on sheet 1, then use a VLOOKUP formula on the second sheet to fill columns D & E.
If you can't move column C - use INDEX formula in combination with MATCH. for example to get Serl. No: =INDEX('Sheet1'!A:A,MATCH(C:C,'Sheet1'!C:C,0))

Wrap the chosen formula in a IF statement to avoid errors e.g. =IF(ISBLANK(C:C),"", [your formula here]<your formula="">)</your>

 

[Gideondb]

Thank you, in block D Sheet 2 ( my sheet Donnie ) this doesnt work : =IF(ISBLANK(C:C),"", [=INDEX('StockMain'!A:A,MATCH(C:C,'StockMain'!C:C,0))]) Sheet 1 is my sheet StockMain.

 

[Gideondb]

Thank you, in block D Sheet 2 ( my sheet Donnie ) this doesnt work : =IF(ISBLANK(C:C),"", [=INDEX('StockMain'!A:A,MATCH(C:C,'StockMain'!C:C,0))]) Sheet 1 is my sheet StockMain. Seem there is a eror with this part (C:C),"", ?

 

[bobsan42]

=IF(ISBLANK(C:C),"", INDEX('StockMain'!A:A,MATCH(C:C,'StockMain'!C:C,0)))

If C:C reference type doesn't work in Excel 2003 you should use:

=IF(ISBLANK(C1),"", INDEX('StockMain'!A:A,MATCH(C1,'StockMain'!C:C,0)))

 

[Gideondb]

Thanks Bobsan Keep on getting same Fault : Microsoft : "We found a problem with the formula.Try clicking insert formulas to fix it, or help for more information on more commen formula problems. Not trying to type a formula " and and more .
Its excel 2013

 

[Gideondb]

The (C1) is in blue with red brackets, the ,"", is highlighted , the bracket here F(I is and red as well as the second last bracket is in red.?
=IF(ISBLANK(C1),"", INDEX('StockMain'!A:A,MATCH(C1,'StockMain'!C:C,0)))

 

[MARK858]

Are your formula separators where you are , or ; ?
Try changing all the , to ;

 

[Gideondb]

Thank you Mark585 it seems the fault is now gone , if i put the code in block C , no joy if tried B and E as well , if i scan the barcode nothing happens .In what block should i place the code ?=IF(ISBLANK(C1);""; INDEX(StockMain!A:A;MATCH(C1;StockMain!C:C;0)))

Are your formula separators where you are , or ; ?
Try changing all the , to ;

 

[Gideondb]

The serl no in sheet 1 My stockmain is[TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl64, width: 103"]BWStock00002[/TD]
[/TR]
</tbody>[/TABLE]
what happens now is that , if i scan it shows this :[TABLE="width: 161"]
<tbody>[TR]
[TD="class: xl65, width: 161"]BWStock6001015506017 the barcode with BWStock ? The serl number was made by auto fill 1 - 3000 and also the BW 0000 before the nomber was made with leading zeros.Should i type it all in with the keyboard ?
Thanks for your help.[/TD]
[/TR]
</tbody>[/TABLE]

=IF(ISBLANK(C:C),"", INDEX('StockMain'!A:A,MATCH(C:C,'StockMain'!C:C,0)))

If C:C reference type doesn't work in Excel 2003 you should use:

=IF(ISBLANK(C1),"", INDEX('StockMain'!A:A,MATCH(C1,'StockMain'!C:C,0)))