I need to prepare a shipment report
One tab is shipment details by listing shipment number, product name / quantity etc). I am required to calculated shipping cost per unit and shipping cost for each item of each shipment.
For example: shipment 20000 - shipped 12 products fm AAA-MMM. (column D Prod Wt is weight of entire product. That means AAA is 5lb, BBB is 1lb...etc). I set this column is to calculated AAA - 147 units *5 = 735lb
another tab is summary of each shipment including Shipment Fee+Misc Fee (sum up of these 2 fees become "Total Fee") Total Quantity of each shipment, # of pallets, shipment weight/total Weight
Shipment weight is included pallet weight. For eg, shipment 20000 - shipment weight is 5058lb but Total weight is 4518lb by summing up all product weights.
Tab: Shipment Details
have set formula under column J "=[@[Total weight of product]]/'Shipment Summary'!$H$2*'Shipment Summary'!$D$2"
but I need to manual change row number of shipment summary row H and D which isn't efficient.
I wonder if possible to use VLook up of "Shipment ID" in order to identity the relevant Total Fee vs Total Weight
Bookmarks