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

#### akvillemaire

##### New Member
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.

 Monday Tuesday Wednesday Thursday Friday Weekly Total Shipped Last Wk End Qty Wk End Available 0 0 0 -45 0 -45 0 45 <-- This is a received shipment (but did not ship -45) 0 0 0 0 0 0 40 40 1 0 2 0 0 3 96 93 0 0 -1 0 0 -1 17 18 <-- this is a return and is reflected accurately

<tbody>
</tbody>

Any ideas?

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Replies
2
Views
746
Replies
6
Views
589
Replies
2
Views
841
Replies
4
Views
458
Replies
0
Views
2K

1,195,650
Messages
6,010,920
Members
441,572
Latest member
keobongda8812

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back