I have a spreadsheet running that shows all outgoing stock in units, the problem is the stock comes in different pack sizes and needs to be charged for the number of picks that have been done to complete the order.

what I need is when 6 units is entered into excel it returns what i would have to pick to get those units i.e 1 full box of 4 and 2 inner's = 6 units as an example.

i have a DATA sheet that holds all the pack sizes and the spreadsheet can VLOOKUP these however it will only return the pack size not the pick Qty.

With outgoing stock in A2 and pack size in B2, try:

=INT(A2/B2)&" full box of "&B2&" and "&MOD(A2,B2)&" inner's"

Awesome thank you that works perfectly. Now to make it a bit trickier would it be possible to add a cell that adds the full carton and inner pick to return a single number?

Maybe like the attached

Attached Files

Boxes.xlsx (16.2 KB, 14 views)

Sweet thank you, I new how to do it but couldn't figure it out. That k you for that.