I am looking for a solution to a problem I am having with this document.
Explanation of the document-
The document has 4 pages –
MRPExport – This is where the purchasing recommendations are pasted
MRP Recommendations with lead – (Main Sheet) - This combines information form MRPExport and Items Sheet and runs some Calculations
Items – This is a list of items from the system which contains some extra information than MRPExport
Exchange Rates – this is used to convert all Currencies into GBP
As you can see I have two totals in AQ2 and AR2, the problem is that these totals are not correct, AQ2 is calculated by - Actual Quantity Column * Cost Column. AR2 is calculated by – Adjusted Quantity Column * Cost Column
These totals are designed to show the total costs of all the purchase orders recommended by the system.
The Problem –
As well as recommending new purchase order (new P/O), this document also recommends to cancel Purchase orders (Cancel P/O) unfortunately the system does not record the prices for the cancellation recommendations only the ‘new P/O recommendations’. As a result the total costs are not accurate.
NOTE - When there is a ‘Cancel P/O’ There is not always a ‘new P/O’ Recommendation.
I need the cost column to display the cost of the item in minus figures for lines that contain the word Cancel in the Message column, AND have the same item on a PO Recommendation so that they will be subtracted from the total.
The Formula needs to do this -
Put a Minus figure of the Cost of the Product Code in column ‘Cost’ Under these conditions –
There is a Cancel Recommendation in the Message Column AND a New P/O Recommendation in the message column for the same Product Code. If there is no New P/O recommendation it should put a 0 in the Cost Column. It should also show a positive Cost for all other items.