Calculation Pallet Movement

[Mitch.Birlea]

Hello,

I'm hoping anyone could help me.

I need help adding something to the file which has been attached.

The F column has drop down menu so i can choose which box i need out of box 1, box 2, box 3 and so on.

what i need to add is when i choose the box i want i need a number to automatically come up in column G. for example the first row, when i chose box 1 i need the number 12 to appear in column G for box 2 i need the number 4 to appear in column G. I need this to happen for every column i have and every column i add. The number which appears in column G will differ for each bed. so for Bed 1 & Bed 2 these number will be different. There are over 1000 more columns which i need to add.

Looking forward to the help.

Thank you!

[Richard Buttrey]

Hi what's the relationship (if any) between the Box number and the number you want to appear.

You say Box1 = 12, box 2 = 4, what about the other boxes? Are the numbers arbitrary or is there some direct relationship which isn't obvious to me at the moment

That said I think the solution will lie in a two column table of Bed & Box permutations. i.e.

Bed1Box1 - 12
Bed1Box2 - 4
Bed3Box6 - ?
...etc.

[Mitch.Birlea]

Hi,

The relationship between the box number & the number i want is the amount of numbers that can fit on a pallet for that particular bed.

For example:

The Smith Bed: Box 1 = you can only fit 25 boxes on a pallet , Box 2 = you can only fit 15 boxes on a pallet, Box 3 = you can only fit 40 boxes on a pallet.

The Flynn Bed: Box 1 = you can only fit 4 boxes on a pallet, Box 2 = You can only fit 20 boxes on a pallet.

The Harris Bed: Box 1 = you can only fit 39 boxes on a pallet, Box 2 = you can only fit 25 boxes on a pallet, Box 3 = you can only fit 35 boxes on a pallet, Box 4 = you can only fit 6 boxes on a pallet.

For each Bed this will be different. and there will be over 1000 beds.

Hope this makes sense

Thanks

[Richard Buttrey]

If there is no common rule that can calculate the number of boxes from the Bed & Box names then you will need to create a lookup table. There is no other way. create your ttwo column table of all permutations of Bedn_Boxn & Numbers and then use a VLOOKUP in column G.

[Mitch.Birlea]

If each bed has a unique code such as the attached, (CASB6CHA) would this be better?

So the code will bring up the correct bed, so for the above code it will bring up the Casey bed box 1. Then the number of boxes that fit on 1 pallet will appear automatically in the opening balance column.

For CASB6CHB it will bring up the Casey bed box 2 & the number in the opening balance will appear.

Hope you can help.

Thank you.

[Mitch.Birlea]

Sorry it is attached now