Excel drop down boxes that populate multiple cells

Hi,
We make labels to go on palets of product.  We would like to make an excel spreadsheat that has one dropdown box once you choose a value from the box it automatically populates another cell with the description of the value selected.  Attached is the label and a small amount of a very large excel spreadsheat of out products. The two columns are SKU# and ITEM.  We will select the SKU# and the Item associated with that SKU should apear in the description area of the label.   Also I would like to be able have a serial number which is used for tracking if necessary that automatically incriments itself after the lable is printed or saved and the serial number can start out as any number lets say 10000.  The serial number area is not on that label yet.
Thanks
Book1.xlsx
pcsonwheelsAsked:
Who is Participating?
 
patrickabConnect With a Mentor Commented:
pcsonwheels,

>How do you know which port it is connected to Ne00 Ne01 ...

The easiest way to figure it out is to switch on the macro recorder and then go to File/Print, select the printer that you want to use and print the label, stop the macro recorder and use the VBA result in the main macro.

Patrick
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello pcsonwheels,

see the attached file. It has a dynamic range that grows and shrinks with the SKU numbers on Sheet2. This range name is used for a list data validation in B2 on Sheet1

The Item description is then pulled with an Index/Match.

The self-incrementing serial number should be a different question.

cheers, teylyn
scratch.xlsx
0
 
patrickabCommented:
pcsonwheels,

Attached is a file in which the label can be printed when the button is pressed - with or without incrementing the Tracking Number.

Hope it helps

Patrick
pcsonwheels-01.xls
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pcsonwheelsAuthor Commented:
Thank you both for your answers.  I wanted to know what needs to be added to the macro to make it print to a different printer than the default printer.  I did some reading about it online and see it should be activeprinter = "\\comp2\hp4000 Ne01:"  How do you know which port it is connected to Ne00 Ne01 ...
Thanks
0
 
patrickabCommented:
pcsonwheels,

To give an equal split of points for very different solutions seems somewhat odd. Do please explain your reasoning.

Patrick
0
 
pcsonwheelsAuthor Commented:
I used the first solution to take care of selecting the data then I used your solution to take care of the printing by adding the macros to the already done shipping label.  I know your solution had the same type of selection process but I had modified the appearance of the shipping label before I used the printing macros.
0
All Courses

From novice to tech pro — start learning today.