Inventory report to reflect increases (reciepts) and decreases (returns) seperately

[akvillemaire]

Hi There,

I have an inventory report, that performs a lookup from each day's inputted data (each day has a sheet where that report is inputted). In the summary sheet, I am looking up the on hand from the day before and subtracting the on hand for today. This gives me the total items shipped each day. The current formula for Thurday as an example looks like this:

=IFERROR(IFERROR(VLOOKUP($A7,Wed!$A:$R,6,FALSE),0)-(VLOOKUP($A7,Thurs!$A:$R,6,FALSE)),0)

The week is then summed and the "Week End Available" is calculated by subtracting the summed shipments this week from the "Last Week End" inventory

The problem is, when I receive a shipment (say 45 pcs), my on hand (from the input sheet) INCREASES, causing my formula to return a negative value, when in reality I didn't ship -45 (which reflects negatively on my total shipped items at the end of the week).

In addition, sometimes there will be a return, or a DECREASE in the on hand, which I DO want to show as a negative and be reflected in the total shipped items.

in summary this gives me two issues... I cannot absolute the shipments because I didnt ship 45 pcs AND I cannot iferror - zero because it will not reflect that I have received a shipment.

[TABLE="width: 624"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: #F2DCDB"]Monday
[/TD]
[TD="class: xl68, width: 64, bgcolor: #EBF1DE"]Tuesday
[/TD]
[TD="class: xl70, width: 64, bgcolor: #E4DFEC"]Wednesday
[/TD]
[TD="class: xl72, width: 64, bgcolor: #DCE6F1"]Thursday
[/TD]
[TD="class: xl74, width: 64, bgcolor: #FDE9D9"]Friday
[/TD]
[TD="class: xl76, width: 64, bgcolor: #F2F2F2"]Weekly Total Shipped
[/TD]
[TD="class: xl76, width: 64, bgcolor: #F2F2F2"]Last Wk End Qty
[/TD]
[TD="class: xl77, width: 64, bgcolor: #F2F2F2"]Wk End Available
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #F2DCDB"]0
[/TD]
[TD="class: xl69, bgcolor: #EBF1DE"]0
[/TD]
[TD="class: xl71, bgcolor: #E4DFEC"]0
[/TD]
[TD="class: xl73, bgcolor: #DCE6F1"]-45
[/TD]
[TD="class: xl75, bgcolor: #FDE9D9"]0
[/TD]
[TD="class: xl79, bgcolor: #F2F2F2"]-45
[/TD]
[TD="class: xl80, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl78, bgcolor: #F2F2F2"]45
[/TD]
[TD="bgcolor: transparent, colspan: 5"]<-- This is a received shipment (but did not ship -45)
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #F2DCDB"]0
[/TD]
[TD="class: xl69, bgcolor: #EBF1DE"]0
[/TD]
[TD="class: xl71, bgcolor: #E4DFEC"]0
[/TD]
[TD="class: xl73, bgcolor: #DCE6F1"]0
[/TD]
[TD="class: xl75, bgcolor: #FDE9D9"]0
[/TD]
[TD="class: xl79, bgcolor: #F2F2F2"]0
[/TD]
[TD="class: xl80, bgcolor: #F2F2F2"]40
[/TD]
[TD="class: xl78, bgcolor: #F2F2F2"]40
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #F2DCDB"]1
[/TD]
[TD="class: xl69, bgcolor: #EBF1DE"]0
[/TD]
[TD="class: xl71, bgcolor: #E4DFEC"]2
[/TD]
[TD="class: xl73, bgcolor: #DCE6F1"]0
[/TD]
[TD="class: xl75, bgcolor: #FDE9D9"]0
[/TD]
[TD="class: xl79, bgcolor: #F2F2F2"]3
[/TD]
[TD="class: xl80, bgcolor: #F2F2F2"]96
[/TD]
[TD="class: xl78, bgcolor: #F2F2F2"]93
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #F2DCDB"]0
[/TD]
[TD="class: xl69, bgcolor: #EBF1DE"]0
[/TD]
[TD="class: xl71, bgcolor: #E4DFEC"]-1
[/TD]
[TD="class: xl73, bgcolor: #DCE6F1"]0
[/TD]
[TD="class: xl75, bgcolor: #FDE9D9"]0
[/TD]
[TD="class: xl79, bgcolor: #F2F2F2"]-1
[/TD]
[TD="class: xl80, bgcolor: #F2F2F2"]17
[/TD]
[TD="class: xl78, bgcolor: #F2F2F2"]18
[/TD]
[TD="bgcolor: transparent, colspan: 5"]<-- this is a return and is reflected accurately
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Any ideas?