Formulas with IF statement to create bar codes

[stormy_00]

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>

 

[StephenCrump]

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
	A	B	C
1	Widget	Widget 2	Widget 3
2	Widget 4	Widget 5	Widget 6
3	Widget 7	Widget 8	Widget 9
4	Widget 10
Barcode

Excel Workbook
	A	B
1	Serial Number	Description
2	NTE231	Widget
3	1254	Widget 2
4	NT351	Widget 3
5	5423	Widget 4
6	NLR921	Widget 5
7	??	Widget 6
8	??	Widget 7
9	??	Widget 8
10	??	Widget 9
11	??	Widget 10
Assets

 

[stormy_00]

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]

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]

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
	A	B	C
1	Widget 1	Widget 2	Widget 3
2	NTE231	1254	NT351
3
4
5	Widget 4	Widget 5	Widget 6
6	5423	NLR921	AAA
7
8
9	Widget 7	Widget 8	Widget 9
10	BBB	CCC	DDD
11
12
13	Widget 10	Widget 11	Widget 12
14	EEE	FFF	GGG
15
16
17	Widget 13
18	HHH
Barcode

Excel Workbook
	A	B
1	Serial Number	Description
2	NTE231	Widget 1
3	1254	Widget 2
4	NT351	Widget 3
5	5423	Widget 4
6	NLR921	Widget 5
7	AAA	Widget 6
8	BBB	Widget 7
9	CCC	Widget 8
10	DDD	Widget 9
11	EEE	Widget 10
12	FFF	Widget 11
13	GGG	Widget 12
14	HHH	Widget 13
Assets

 

[stormy_00]

Yes!!!!!!! Thank you!!!!