Excel 2007 : IF Statement for Data Validation Selections + Page Numbering

Every month we have to make shipments to one of 15 warehouses, and I need to print labels for each carton being shipped, this means 1000's of labels a week. The way we have been doing it (using Word) is way to cumbersome and Excel can do it better if you can help me. The label has to contain the following information: The Warehouse, Street Address, City/State/Zip, Date, Shipment Number, Item #, and Box # of Boxes.

The user opens the workbook and enters or selects the data on sheet 1, which will then print to a 2x2 thermal label printer.

I am running into 3 problems getting this to work the way I want to, though I am open to other ways of doing it I think I am on the right track and with your help will complete this quickly.

Problem #1 - Getting address to auto populate into cells.
On sheet 2 of the workbook I have the list of warehouses, address, and city/state/zip in columns X,Y and Z respectively. On sheet 1, Cell C2, I have a drop down list that uses Data Validation: List referencing "=Sheet2!X1:X15". So when the user clicks on cell C2, he or she can choose only one of the warehouses using a drop-down list that appears in Sheet 2 Column X..

Question: When the user selects in a Warehouse from the list, what code do I put into cell C3 to get it to show the contents of the corresponding street address? For example if they chose Warehouse 9, that means they chose Sheet2!X9, so we need cell C3 to show the contents of cell Sheet2!Y9 and cell C4 to show the contents of sheet2!Z9. If they chose Warehouse 15, obviously it needs to show Y15 and Z15, respectively. What is the best code (I've tried to build on code that starts like =IF(C2=Sheet2!J4,B64) but I can't get any of them to take.

(FWIW I also tried putting the entire warehouse, address, city/state/zip into one cell on sheet 2 while using ALT+Enter to break the lines, but it doesn't replicate the line breaks in cell C2. If there is some way to make the Data Validation paste the exact formatting including the line breaks that is an alternate solution).

Problem #2 - Box Count
Each label has to print Box Number of Total Boxes. If I am shipping 40 boxes to Warehouse 1, that means I need to print 40 labels and each label has to list which box number it is by by printing "Box 1 of 40" on label #1, "Box 2 of 40" on label #2, "Box 3 of 40" on label 3 and so on. Does this have to be done in VB code? Or is there some other way? Anyone got this code handy, or can whip it out for me with a quick explanation how to implement it?

Problem #3 - Label Size
All this data is to print on a custom 2x2 thermal label printer. I am testing it, however, on a .8.5x11 laser printer. Any idea how I can customize the label size? I am admin of my computer, but when I go to print settings the customize label is greyed out and I get a "you do not have permission to save system settings on this computer" message.

I appreciate it. These 3 hurdles are between me and a happier work environment. The rest I've got down. Thanks in advance for your help.

OK I got a solution for those interested - it may come up in a search some day. I found it on Yahoo of all places and only made tiny changes to it: Link to Original Code

What it does is print the copy number onto each page you print. If you need to print multiples copies of the same sheet and each copy needs to have a unique number on it, this one way to do it. This prints the copy number into a cell - in this case cell D10.

Sub Print_X_Copies()
Dim TtlPrint As Single
TtlPrint = 1
HowMany = InputBox("Please enter the total number of labels" _
& " of copies you wish to print.", "How Many?")
If HowMany = "" Or HowMany = 0 Then
MsgBox "No valid entery made.", vbOKOnly, _
"Number > 0 Required"
Exit Sub
Else
For i = 1 To HowMany - 1
If i = 1 Then
Range("D10").Value = "1"
ActiveWindow.SelectedSheets. _
PrintOut Copies:=1
End If
Range("D10").Value = "Box Number: " & TtlPrint
ActiveWindow.SelectedSheets. _
PrintOut Copies:=1
TtlPrint = TtlPrint + 1
Next
End If
Range("D10").Value = ""
End Sub

I saved this as a module and a macro for sheet 1.
I created a button on sheet 1 that has the word "PRINT" on it and then linked the macro the the button. Now when the user pushes PRINT it asks "How many sheets" and you enter the total number of labels you want printed.

In Cell D10 it prints the copy number. I used the layout/formatting inside the cells to add the other stuff I need like "of". And the user still has to type the total number of cartons in cell D11 before printing, then print exactly that many copies.

Thanks for everyone's help and I hope this helps other people and that I put enough description in this post that people will find it in a web search...