Vlookup or IF Function...HELP!

[barkpi1597532]

Hello,

I have created a master inventory excel sheet to keep track of all my inventory. It consists of 4 tabs; Lists, Shipping Log, Receiving Log, and Inventory

On my Inventory sheet, I am wanting to add any merchandise from the receiving log to the previous inventory on the 'Lists' tab. At the same time, I am wanting to subtract any orders that are shipped on the Shipping Log sheet. I have tried a combination of Vlookup, IF, and SUMIF functions and nothing is giving me the correct information. What formula can I use in column E on the Inventory sheet to capture this information? Any help is appreciated!!

 

[barkpi1597532]

My first thought was to create a Vlookup formula in cell E4 on the 'Inventory' tab to pull the original data from the 'Lists' tab and add the IFERROR function to replace any errors with 0. (=IFERROR(VLOOKUP([@Item],Lists!N6:O14,2,FALSE),0))

My next thought was to maybe do a SUMIF function to add all of the values of the individual products on both the 'Shipping Log' and the 'Receiving Log'. I created columns in both the 'Shipping Log' and the 'Receiving Log'. (Pictures are below).

I was able to have the items from the 'Shipping Totals' subtract from the original quantity from the 'Lists' tab with the following formula (=IFERROR(IF([@Item]='Shipping Log'!O7,(VLOOKUP([@Item],Lists!N6:O14,2,FALSE)-'Shipping Log'!P7)),0))

By using the same formula above and altering to correspond to the 'Receiving Log', it will give the the information that I need (=IFERROR(IF([@Item]='Receiving Log'!L6,VLOOKUP([@Item],Lists!N6:O14,2,FALSE)+'Receiving Log'!M6),0)).

How do I combine the two formulas to run simultaneously?

 

[barkpi1597532]

This is the formula I have create that combines both the addition of the receiving tab and the subtraction from the shipping tab. However, it is not calculating correctly. It looks like it is only picking up the first part of the formula, when I am needing it to pick up both parts.

=(IF([@Item]='Receiving Log'!L6,VLOOKUP([@Item],Lists!N6:O14,2,FALSE)+'Receiving Log'!M6,IF([@Item]='Shipping Log'!O7,VLOOKUP([@Item],Lists!N6:O14,2,FALSE)-'Shipping Log'!P7,0)))

 

[barkpi1597532]

After several attempts, I was able to create a formula that worked. If there are any easier methods, please feel free to mention them!

=IFERROR(VLOOKUP([@Item],Lists!N6:O14,2,)+'Receiving Log'!M6-'Shipping Log'!P7,0)