# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

## printchick26

MASTER SHIP LIST BEVMO FROM ANDREA.xlsPacking Slip Template.xlsxFor Excel Forum .xlsxmaster list cartons separated for excel forum..xls

Hello All, 

I'm looking for some much needed assistance. 
I work for a company that ships large volumes of product to immense numbers of destinations. 
I need to figure a way to quickly create packing slips from a single Master Ship List. 
Each Row below the header is a different store. 
Each Column is a different product - not every store gets every product. 
NO zero quantities on the packing slips. 

Oh, and to throw in a wrench - Every carton has to have its own packing slip  some stores get 3-5 cartons. BUT here's one of the nastiest parts - some of the stores will have 12 of the large signs. Only 8 will fit in a carton, those 12 may be spread out between the 3 "groups". I wont need 3 packing slips for 12 signs, only 2. 

I have attached what I've been MANUALLY creating for the past few months using copy/paste (as the poor soul before me) and an old master list, a copy of the master list with the "standard" cartons separated by blacked out columns, AND one store's finished MULTIPLE packing slips - so you can see how horrible this really is. 

Quite honestly I'm fed up with it. I literally spend 30+ hours a week IN OVERTIME  :EEK!:  creating these when these big projects ship and it's killing me. Our IT people were dumbfounded when I asked them for help. PLEASE BE MORE CLEVER THAN THEM! 

The final does not need to look like what I've been creating, its just an idea - if it will work. 

Any and All help is GREATLY appreciated!!

----------


## xladept

Hi printchick,

Can you run through the procedure?  You must have some way of knowing which product goes where.  Do you mark up the template and then get the info from the master or what do you actually do??

----------


## printchick26

Master List - Excel Forum .xlsxManipulated Worksheet - Excel forum .xlsxMaster List - Excel Forum .xlsxManipulated Worksheet - Excel forum .xlsx


Well the long boring drawn out version is this: 

When we receive the Master List,I format the sheet so every cell is filled with a color, based on values (1's are red, 2's are blue etc..) then I organize the sheet so all of the items with like sizes are together, and I insert blank/blacked out columns to keep all sizes separate. Then I move the customers up and down the list to create similar "pack outs", enter some more blank/blacked out rows, and finally I move any oddball columns to the end of the sheet as a "pick and pack" section. 

I fill in my template based on spacing convenience only. The only constant is the top portion of the slip is always the "kit" or Pack out, and the bottom of the slip is always the "pick and pack". BUT, each time I create different "kits/packouts" and it has a slightly different layout, as the product elements, quantities and versions are different every single promotion. i.e. One promotion we will have 89 product elements and the next 64 and the third 114. 

As I fill in the template I go down my, now unrecognizable, list and first copy/paste that stores ship to information then copy/paste that particular stores "kit/packout" values and finally the "pick and pack" values. THEN, I check every single packing slip against the master list to make sure I have no typo's, size or quantity/version errors. 


Not sure if it will help but I've attached another "master list" and finally my manipulated "pack outs" list to kind of give you an idea of how I'm moving things around. 

Sorry for the delayed response, I haven't been able to eat my lunch yet its so busy here!

----------


## xladept

Hi printchick,

There are four attachments - is that for two attachments twice, or are they different (I can't open them all because two have the same name)?

----------


## printchick26

It's probably just the same 2 files twice. I got an error when I was trying to attach them.

----------


## xladept

I don't see my beer on there :Confused:

----------


## printchick26

Lol sorry!

----------


## xladept

> PLEASE BE MORE CLEVER THAN THEM!



I'll try but my mind is blank - what information must the packing slip contain - and do you ship to each store or ship to several stores in an area?

----------


## printchick26

Each slip must contain the respective store's ship to information. And list each item being shipped with version size, description and quantity.  

And we do ship direct to each store. :-(

----------


## xladept

The ship to information is on another sheet?  What is the sheet name and do you get that information by the store location?

----------


## printchick26

The ship to information is in a separate sheet. I can't upload it due to confidentiality issues - sorry.

----------


## printchick26

Oh, and the store number is what I use to pick the address :-)

----------


## xladept

I'll need to know how to get it - I don't need to know what it is!

----------


## printchick26

Not sure I understand - 

It's a very simple list on a separate sheet. 
Column 1 - store #
Column 2 - ID/location
Column 3 - street address 
Column 4 - city,state, zip 

Does that help? Or should I make a mock up and upload it?

----------


## xladept

That should do it - what is the name of the book and of the sheet?

----------


## printchick26

The workbook is "Store List with Addresses for Packing Slips" 
The sheet is "Sheet 1"

----------


## printchick26

The workbook is "Store List with Addresses for Packing Slips" 
The sheet is "Sheet 1"

----------


## xladept

Thanks - now I need to ponder it for a bit - hopefully I'll have something for you today.

----------


## xladept

Well - not today.  I'm thinking that all we need for a packing slip entry is the header - the "ship to" and "packed by" are standard - so, if we build a packing slip using the headers for each store number, should we put each slip in a sheet on the master or should we use a separate book??

----------


## xladept

Hi Printchick,

I've gotten this far - it's only a start - but I need you to criticize the result as to placement and accuracy or whatever else you'd like it to be.

This code I've put into an outside book, a macro book(if you will):




```
Please Login or Register  to view this content.
```


And I used these three books - output is on the Master please see if the output is correct.

Store List with Addresses for Packing Slips.xls
Packing Slip Headers.xlsx
MASTER SHIP LIST BEVMO FROM ANDREA.xls

----------

