# Formulas with IF statement to create bar codes

#### stormy_00

##### New Member
Hi

I would like to take our inventory and create bar codes. However, my formula moving from each row, doesn't go in row order.

For instance:
Row 1, Col A has =IF(ISTEXT(Assets!I2),Assets!I2,) then
Row 1 Col B has =IF(ISTEXT(Assets!I3),Assets!I3,) and
Row 1 Col C has =IF(ISTEXT(Assets!I4),Assets!I4,).

Moving down to the next row, row 2 starts with

=IF(ISTEXT(Assets!I6),Assets!I6,) and not I5. Not sure why.

Barcode Spreadsheet looks like this:
 Widget formula: =IF(ISTEXT(Assets!B2),Assets!B2,) Widget 2 formula: =IF(ISTEXT(Assets!B3),Assets!B3,) Widget 3 formula: =IF(ISTEXT(Assets!B4),Assets!B4,) Widget 4 formula is wrong: =IF(ISTEXT(Assets!B6),Assets!B6,) Widget 5 =IF(ISTEXT(Assets!B7),Assets!B7,)

<tbody>
</tbody>

Inventory spreadsheet looks like this:
 Serial Number Description NTE231 Widget 1254 Widget 2 NT351 Widget 3 5423 Widget 4 NLR921 Widget 5

<tbody>
</tbody>

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

I'm not really clear what you're trying to do here, or how it relates to barcodes.

But I'm guessing something like this?

Barcode!A1: =IFERROR(INDEX(Assets!\$B\$2:\$B\$11,1+3*(ROW()-ROW(\$A\$1))+COLUMN()-COLUMN(\$A\$1)),"")

Excel Workbook
ABC
1WidgetWidget 2Widget 3
2Widget 4Widget 5Widget 6
3Widget 7Widget 8Widget 9
4Widget 10
Barcode

Excel Workbook
AB
1Serial NumberDescription
2NTE231Widget
31254Widget 2
4NT351Widget 3
55423Widget 4
6NLR921Widget 5
7??Widget 6
8??Widget 7
9??Widget 8
10??Widget 9
11??Widget 10
Assets

#### stormy_00

##### New Member
I apologize. Here is a clearer picture of what I need:

Row A will have the description of the item
Row B will have the serial number
Row C will have the bar code

I have set up three columns so that it will print decently to scan the bar code. Each store has different assets to count. For instance, once store my have only 25, another 53, another 100, etc. Each store will run an inventory report and save it as an Excel spreadsheet. I would like for the store to be able to copy and paste the inventory into the Assets tab and "generate" barcodes as stated above automatically on the Barcode tab. Then they just print off the sheets and start scanning.

The problem that I ran into is copying my formula into each cell. I ran into the same problem with your formula.

Thanks

#### stormy_00

##### New Member
When I copy your formula in the first row (descrption) it pastes the corect information. I changed your formula to work in the 2nd row for the serial number. The third row is the bar code which generates from the 2nd row which works fine. So A1:A3 is for 1 item. B1:B3 is for another item and C1:C3 is for the third item. I skip a row (row 4) and go to row 5. Row A5 is the descrption, A6 is the serial number and A7 is the barcode. Etc for Column B & C.

When I copy the formulas from A1:A3 and paste it in A5:A7, B5:B7 & C:5:C7, it pastes the wrong inventory items.

#### StephenCrump

##### MrExcel MVP
Like this?

A1: =IFERROR(INDEX(Assets!\$B\$2:\$B\$14,1+0.75*(ROW()-ROW(\$A\$1))+COLUMN()-COLUMN(\$A\$1)),"")
A2: =IFERROR(INDEX(Assets!\$A\$2:\$A\$14,1+0.75*(ROW()-ROW(\$A\$1))+COLUMN()-COLUMN(\$A\$1)),"")

Excel Workbook
ABC
1Widget 1Widget 2Widget 3
2NTE2311254NT351
3
4
5Widget 4Widget 5Widget 6
65423NLR921AAA
7
8
9Widget 7Widget 8Widget 9
10BBBCCCDDD
11
12
13Widget 10Widget 11Widget 12
14EEEFFFGGG
15
16
17Widget 13
18HHH
Barcode

Excel Workbook
AB
1Serial NumberDescription
2NTE231Widget 1
31254Widget 2
4NT351Widget 3
55423Widget 4
6NLR921Widget 5
7AAAWidget 6
8BBBWidget 7
9CCCWidget 8
10DDDWidget 9
11EEEWidget 10
12FFFWidget 11
13GGGWidget 12
14HHHWidget 13
Assets

#### stormy_00

##### New Member
Yes!!!!!!! Thank you!!!!

Replies
1
Views
408
Replies
1
Views
268
Replies
0
Views
304
Replies
3
Views
2K
Replies
6
Views
293

1,191,550
Messages
5,987,225
Members
440,085
Latest member
MBecker79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back