Forum Discussion
Excel Formula for box numbers
Need assistance to setup an box nr. pack list The maximum quantity that can be packed in a box is 1500 as per the list. After the first box of the first variety is filled up, any incomplete amoun...
A_SIRAT I have attached a file with an equation based table next to your desired table. I added a column to you original table called **bleep** Ord for the cumulative total of ordered parts. I also did not take into account the individual 'Max Pack rate' since you said it is max of 1500 and I didn't know what to do if product Jenny had been max 1500 but Sylvia was only 500; would line 4 be 500 or something less since there is already 200 Jenny in the box. That said the 3 equations are:
Box Number (col K):
=IF(MOD(SUM(M$1:M2),1500),K2,K2+1)
Variety Name (col L):
=XLOOKUP(SUM(M$2:M2)+1,Tabelle1[**bleep** Ord],Tabelle1[Variety Names],"error",1)
Box nr (col M):
=MIN(XLOOKUP(SUM(M$2:M2)+1,Tabelle1[**bleep** Ord],Tabelle1[Qty ordered],"error",1)-SUMIF(L$2:L2,L3,M$2:M2),1500-MOD(SUM(M$2:M2),1500))