# EXCEL Sheet and blocks

#### Gideondb

##### New Member
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

 A B C D Serl. No Bar code Bar code no Item name BWStock00001 6001599054393 Diary BWStock00002 6001015506017 tin BWStock00003 6001007316884 Paper Towl BWStock00004 6001007160937 toilet paper BWStock00005 6001087006033 HAMMER

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>
2 SHEET

 A B C D E F G Barcode no Serl. no. Item Name Units Price 1 2 3 4 5 6

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### bobsan42

##### Well-known Member
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>

Last edited:

#### Gideondb

##### New Member
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

##### New Member
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

##### Well-known Member

Code:
``=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:
Code:
``=IF(ISBLANK(C1),"", INDEX('StockMain'!A:A,MATCH(C1,'StockMain'!C:C,0)))``

Last edited:

#### Gideondb

##### New Member
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

##### New Member

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

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

#### Gideondb

##### New Member
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

##### New Member
The serl no in sheet 1 My stockmain is
 BWStock00002

<tbody>
</tbody>
what happens now is that , if i scan it shows this :
 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.

<tbody>
</tbody>
Code:
``=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:
Code:
``=IF(ISBLANK(C1),"", INDEX('StockMain'!A:A,MATCH(C1,'StockMain'!C:C,0)))``

Replies
0
Views
231
Replies
1
Views
220
Replies
3
Views
261
Replies
1
Views
194
Replies
7
Views
311