Formula Help

[CarlW1179]

I'm currently try to create a spread sheet for our dispatch department.

I am trying (and failing) to create a sheet that works out how to fill pallets.

Our vans hold 4 pallets holding  2@32 baskets & 2@40 baskets.

Our basket values per product are e.g. and i want to work out that product 1 & 2 + 6 baskets (32) from product 3 would go on pallet 1.

30 of product 3 + 2 basket from product 4 (32) would go on pallet 2 etc.

Product 1 12

Product 2 14

Product 3 36 

Product 4 29

Product 5 38

i want the sheet to tell me what pallet to amount of each product on to fill the pallets.

Sorry if this is long winded.  Any help appreciated

[#2]

@CarlW1179 Please see the attached workbook for the complete solution I came up with. It uses the SCAN function to calculate the product allocations for each pallet. Hopefully this is what you were trying to accomplish...

[#3]

@djclements 

That's exactly what I was trying to achieve rank you so much.

Only issue I now have is that when I change the Basket column the cell comes up with #NAME?

Really appreciate your help with this.

[#4 · Accepted solution]

@CarlW1179 No worries. The formulas I used will only work with Excel for MS365. If you're getting the #NAME? error, it's a pretty strong indication that you're using an older version of Excel. I've attached another copy of the workbook, using the same logic, but with functions that should be compatible with any version...

[#5]

@djclements
Your a star that's absolutely spot on.

Thanks again