Add/Subtract columns from tab to tab in same file

[horsehead7]

I have a formula that works for adding all quantities of a specific part number on order from "open orders" tab to the "Aircraft Inventory" tab. It populates the "Total Qty on Hand" column in the "Aircraft Inventory" tab. Formula is: =SUMIF('Open Orders'!D,'Aircraft Inventory'!A:A,'Open Orders'!B:B). What I need to add to this formula is in the "Open Orders" tab I have a quantity received column (P). I need to subtract this quantity from "Open Orders" column B (total qty ordered) to show the current qty still open in the "Aircraft Inventory" tab. I have tried a few things but I can't seem to get it to work.

 

[horsehead7]

For some reason a smiley face keeps coming up on the formula I posted above. It is covering " : D".

 

[Becky]

I'm not sure I understand what you're trying to do, could you give a little more detail? On the face of it you may want to look at the criteria you have specified for the criteria it is currently an entire column whereas I imagine you want it to be the part number.

 

[horsehead7]

For some reason a smiley face keeps coming up on the formula I posted above. It is covering " : D".

I'm not sure I understand what you're trying to do, could you give a little more detail? On the face of it you may want to look at the criteria you have specified for the criteria it is currently an entire column whereas I imagine you want it to be the part number.

I have an inventory file. In that file I have several tabs of information. In this particular situation I am pulling info from the open orders tab to the inventory tab. On the open orders tab there could be 2 or more orders for the same part number for different aircraft and/or stock. On the inventory tab I have a column that shows total quantity on order. What the formula does is look to the open orders tab for the part number no matter how many times that part number may be loaded. It then adds the total quantity from all of the entries for that part number and displays the total in the total on order column in the inventory tab. Works great. Problem is that on a specific order there may be 4 requested and only 2 come in (waiting on the other 2). If that were the only request the total on order would still show 4 open even though 2 have been received. There is another column in the open orders tab that shows total received. What I need is to adjust the formula to add all received quantities for that part number and then subtract it from the original total requested number thus giving me the outstanding quantity still open. This is the quantity I want to pull from the open orders tab to the inventory tab.