Populating 3 different Packing Slips from Master file.

I'm hoping to get some help with a formula that will allow me to populate 3 different Packing Slips with information pulled from a separate Master Distribution List.

Here is a screengrab of the Master List which will help illustrate what I need to have happen:

http://imageshack.us/photo/my-images...rtemplate.jpg/

Row 8 has the code for Packing Slip 1, 2 or 3 for the 3 different shipping kits.

Branch # is the leftmost column.

So say we're looking at Packing Slip 1:
1. We want to enter in the Branch number.
2. From that, it must find find every column with Packing Slip *1* and then see if there is a quantity in that column for that branch.
3. If there is a quantity it must put that quantity in our Packing Slip, followed by the Creative (whatever happens to be the overarching creative in Row 4), then whether it is a "Window" or "Backlit" (Row 5) and then finally the size (Row 7).
4. If there isn't a quantity, it must continue across the Master List until it finds one, putting each one on a separate line.

So for example if we were to enter in Branch 1 into Packing Slip 1, the first item it should enter in the list would be...

1 Creative Window 48"x72"

And if we were to enter Branch 6 into Packing Slip 2, the first item it should enter into the list would be...

4 Creative Window 30"x48"

So this is hideously complicated and I'm having a tough time even wrapping my head around how to structure this thing. So any help here would be awesome!


Thanks,

MJ.

Originally Posted by arlu1201

It would be great if you attach a sample workbook. It will be easier for us to understand your issue.

No problem.

Thanks!

Originally Posted by arlu1201

I understand now how to get the data. But where should the entries be posted? You have not mentioned that.

Our Packing Slip layout is still wip, but here's a rough layout. This will go as sheet 2 into the Master File.


Thanks again!

MJ

Originally Posted by arlu1201

Ok. This is pretty complicated but we can work something out. I have a question. You have shown 1 packing slip in the attachment above. For packing slips 2 and 3, should the data be in separate tabs? Or in the same tab?

First off, thanks a million for having a look at this - it is pretty complex. And as for Packing Slips 2 & 3, they were going to go into separate tabs.

Thanks once again,

MJ.

Originally Posted by arlu1201

Our forum was down due to the upgrade and i couldnt assist you earlier. Is this problem resolved?

I took one more quick stab at it, but couldn't wrap my head around it. Since then, it's just been too busy for me.

So any help you can provide would be awesome.


Thanks,

Mike.

Originally Posted by arlu1201

I can assist you. Its just that we lost your file during the upgrade process. Can you please attach it again?

You bet. I included one of the packing slips as sheet 2...

Test_for_example.xlsx


Mike.

Originally Posted by arlu1201

I have a few questions - You mentioned that you will enter the branch no in the packing slip and the other details should fall in. Where will the branch no be entered? Which cell?

Also, the layout of the packing slip (sheet2) says Quantity, Description, Pieces. I understand Quantity will have the number that is in the grid in row 10 onwards. Description will be whether Creative Window 32x48 etc...what about Pieces...what data goes there?

The "Customer #" should actually be the Branch number, so that should be entered in F17. "Pieces" I forgot to mention - it's manually entered in so it's nothing you have to worry about. It's the number of packages that leave the door. For example, 4 small posters might all go into 1 package (1 piece).