+ Reply to Thread
Results 1 to 20 of 20

I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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 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!!
    Last edited by printchick26; 08-06-2013 at 05:36 PM. Reason: Was told my titled didn't accurately describe my problem.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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??
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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!

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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)?

  5. #5
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    I don't see my beer on there

  7. #7
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    Lol sorry!

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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?

  9. #9
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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. :-(

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  11. #11
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  12. #12
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  14. #14
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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?

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  16. #16
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  17. #17
    Registered User
    Join Date
    08-06-2013
    Location
    Waukesha, WI
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

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

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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??

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: I need to create HUNDREDS of packing Slips... from a single spreadsheet - HOW???

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Making Packing Slips With Excel Data
    By elcade in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-15-2016, 07:05 AM
  2. Text Filter-begin With... (multiple Begin Withs?)
    By hariexcel1987 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-02-2013, 02:12 PM
  3. [SOLVED] Help: need to make packing slips from excel sheet
    By lola12 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-21-2012, 11:18 AM
  4. Populating 3 different Packing Slips from Master file.
    By MJX in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-22-2011, 11:09 AM
  5. Text Filter-Begin with... (multiple begin withs?)
    By flyflipper in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-16-2010, 06:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1