I took a little longer route to what you want. Let's see if you like it.
The first thing I did was to make your Main workbook's tabs visible. In the attached workbook, look at the tabs at the bottom of the application screen. You will see a slide, just between the left-arrow of the horizontal scroll bar and the tab for adding a worksheet. You can drag this slide left and right. In the workbook you posted it was pulled all the way to the left. Therefore your worksheet's tab wasn't visible. (Probably done intentionally by someone)
Next, I added a blank worksheet which Excel called 'Sheet1'. This sheet contains the solution and I shall tell you more about it in a minute.
Finally, I made a copy of that sheet and called it 'Template'. This is your backup. The idea is that you might very well damage 'Sheet1' while working with it. In that case, delete 'Sheet1', make a copy of 'Template', call the new copy 'Sheet1' and you should be back in business.
Now, you will see two groups of 2 lines each in Sheet1. The difference is in columns A and B. In rows 1 and 2 you have a setup where you can enter a number in column A, in rows 7 and 8 the number should be entered in column B.
- You can copy any of the rows of either type to any row in your sheet.
There is no need to keep the groups together or separate. - The formulas in column A and B are different. The formulas in columns C:F are identical. You can copy them horizontally, if you like. The idea is to let you copy entire rows vertically.
- You can write numbers in the cells with white font on red background.
You don't need to write the leading zeros in column A. - Note that the question marks in the red underlaid cells (either column A or B) will appear only if the cell value = 0, but not when the cell is blank.
For now, you have to enter the number manually, in the correct column of the correctly formatted row, and the text will appear in the entire row. In a blank row columns C:F will display "Blank" until you enter a valid number. In the future you might enter the numbers using your scanner. That should be a simple thing to accomplish.
Note that there are some problems with your data sheet 'Main'. In column A every number must have 14 digits, including leading zeros. There are some numbers which don't match this pattern. They will not be found.
"J A C K S O N" will not be found because it is not a 14-digit number. In column B "xxxxxxx" will not be found. However, the formulas in my solution will look for a number in column B if no number is found in column A. Therefore "xxxxxxx" will never cause a problem because there is a valid number in column A.
Now you will have to teach your label printing software to use the sheet. Unfortunately, this site didn't allow me to download your blank workbook. Therefore I might have missed something about its formatting. Anyway, ..
- Your printer software will probably look for the first sheet in the workbook.
That could be 'Sheet1' or it could be the first sheet in the workbook - and never mind its name.
Actually, I suspect the latter. Right-click on the 'Sheet1' tab, select "Move or Copy" and move it to "Before Main". That should make sure that your printer will read from this sheet. - I presume that your printing software will start reading in row 1 and continue reading until it encounters a blank row. Therefore it is possible that your printing software might ignore the items below row 3 in my attached Sheet1.
- Worst case, you can always export the solution in 'Sheet1' to a single tab separate workbook (right-click on the tab, Move or Copy, and select a New Workbook). You may have to Copy/PasteSpecial -> Values to change all formulas to hard values before printing.
Since I am not a regular visitor to this forum anymore I invite other experts to help you in case you need for assistance. Good luck.
You do not have the required permissions to view the files attached to this post.
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)