EXCEL Sheet and blocks

Gideondb

New Member
Joined
Feb 24, 2019
Messages
23
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. NoBar code Bar code no Item name
BWStock000016001599054393 Diary
BWStock000026001015506017 tin
BWStock000036001007316884 Paper Towl
BWStock000046001007160937 toilet paper
BWStock000056001087006033 HAMMER


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

A B C D E F G
Barcode noSerl. no. Item NameUnitsPrice
1
2
3
4
5
6

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

Some videos you may like

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
Joined
Jul 14, 2010
Messages
1,343
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
Joined
Feb 24, 2019
Messages
23
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
Joined
Feb 24, 2019
Messages
23
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
Joined
Jul 14, 2010
Messages
1,343

ADVERTISEMENT

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
Joined
Feb 24, 2019
Messages
23
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
Joined
Feb 24, 2019
Messages
23

ADVERTISEMENT

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
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Are your formula separators where you are , or ; ?
Try changing all the , to ;
 

Gideondb

New Member
Joined
Feb 24, 2019
Messages
23
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
Joined
Feb 24, 2019
Messages
23
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,060
Members
409,682
Latest member
HisHailo

This Week's Hot Topics

Top