We pick orders for different customers. I need to work out when a customer orders a certain amount of a particular product how many pallets, cartons, inners and eaches were picked to get to this quantity.
If you look at the 1st tab ( 3PL Sale Line Extract ) LINE 1 - customer ordered Item No. 855236 (column N) and we picked 5306 units (column R).
Now if you look at the 2nd tab ( Product UOM ) and look at line 49. You can see the same Item no. 855236 and can see how many units are on a full pallet, how many units in a carton, how many units in a inner and an each is 1 unit.
For this item no. 855236 the below amount are in each unit of measure :
Units on a pallet is 4800
Units in a carton is 240
Units in a inner is 10
Units in a each is 1
So to picked 5306 units I would have picked :
1 full pallet = 4800 units
2 cartons = 480 units
2 inners = 20 units
eaches = 6 units
TOTAL = 5306 units
I need to be able to them get full tally for every single product picked on the day. So ever line on the 3PL Sales Line Extract tab needs to reference the Product UOM tab and then tally the total in the last tab UOM Charging.
I need the formulas to cover to line 5000 on the 3PL Sales Line Extract Tab (incase we have busy days and a lot is picked ) & the formula on the Product UOM tab to cover to line 1000 ( incase new products get added to the range ).
Each day I want to paste the new data into the 3PL Sales Line Extract Tab and run a macro which automatically tallies the total of pallets, cartons, inners and eaches picked for the day in the UOM charging tab.
IMPORTANT : I need this is the next 2-3 hours so speed is of the essence for this job.
I hope this is enough information