Autofill info from different worksheets and create a BOL

[daniel.midwood]

Hello, I am Daniel, and I humbly ask for your help.

I usually am able to figure all this kind of stuff on my own, but I hit a roadblock here, kind of complicated.

I work for a company that ships to 4 different big retailers like Walmart, Family Dollar, etc.

I keep all the info in a little excel file where all of the orders and inventories are kept, along with individual warehouse addresses to ship to.

Now, when we are given the ok to ship the orders, we call the trucks. This leaves me with an hour; from the time we call this trucks up to the time they come to pick up the merchandise, to create the BOL's (Bill of Ladings). Sometimes up to 52 BOL's have to be typed in less than an hour. I have a template of the BOL in Excel, but every order needs to go to a different address, with a different PO, a different number of cases, or truck, etc etc.

Right now I scramble a lot in trying to make this BOL's, and often times I make mistakes. But all the correct information, all of it, is already typed into the inventory itself, there is a lot of time wasted just in going back and forth refilling all this info in the BOL template and print.

This is what I need to know: Is there any way to store about 200 macros(each with its own individual shortcuts); or to create a little script that ask the user for input and then auto-fill certain fields from other worksheets so I can, for example, type in the PO number of a certain order and it will fill the number of cases from one worksheet, the address from another worksheet, the date from a different one, etc etc, automatically?

I have tried Macros but it seems that the shortcuts will be less than 60. I need about 200 or so.

Thank you in advance.

[Legend Rubber]

can you attache a sample workbook with instructions?

[daniel.midwood]

TEST1.xlsx

can you attache a sample workbook with instructions?

Im sorry, I am attaching the work book now, I just typed a very detailed message for like 10 minutes and it didn't post.

Anyways here is the sample book.

Basically I would like the worksheet named "sheet 2", to prompt me for a PO number.

Po numbers are unique, with that info, it would look through the worksheets called "Walmart", "Walgreen", etc.

Once the PO is found, Excel would populate the yellow fields in "sheet 2" with the correspondent info of that particular PO number.

Each worksheet has a "store code" column. With the store code located in the same row as the PO number, it then would look for the blue block info such as address from the "Database" worksheet and fill automatically the Blue space in "sheet 2" with the info in database.

Is it understandable?

[Legend Rubber]

not really...
You would need to have the info on each worksheet (CVS, DollarTree, etc) to be in the same columns... eg. PO# is always in Column C etc...
is this possible...?

[Legend Rubber]

not really...
You would need to have the info on each worksheet (CVS, DollarTree, etc) to be in the same columns... eg. PO# is always in Column C etc...
is this possible...?

[daniel.midwood]

not really...
You would need to have the info on each worksheet (CVS, DollarTree, etc) to be in the same columns... eg. PO# is always in Column C etc...
is this possible...?

Oh yes, I could not post the 2013 inventory. The new one, in the works, has all the info and data in the same columns for all sheets. Like all PO's in each sheet are in column "A". I figured before that for something like that to work I had to put all the info in the same column.

I started doing it already.

I will attach tomorrow a small sample of the new one.

You've given me hope thou

[daniel.midwood]

Ok as promised, here are the files. This is the shell of the new inventory, with all columns for the same data in the sheets. the second attachement is the BOL excel, bu I could not attach it to the original file because it becomes bigger than the size allowed.

Thank you in advance