Hi,
I am struggling to come up with a formula to calculate total cost of goods value based on qty available in different locations and taking into account orders already taken for the same product, so I am hoping someone in this forum could help me out 
I am attaching sample data - "Cost of Goods" column D is qty ordered and the "Unit Rate" tab are various rates I would like to use. So firstly I would like to consider rates in the warehouse, then in transit and once no stock is available in these locations use the latest rate card to place new orders.
Thanks in advance.