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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)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
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

patrickabCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.