Formula/VLookup to calculate Running total of Master Inventory Sheet

Good Evening,
I was looking for some help with either a formula or VLOOKUP solution to help keep a running inventory on items in a wherehouse. I have spent hours doing research and trying formulas, but to no avail. Here's the scoop: i have a workbook with a master inventory sheet that lists Item Type, Name, Description, Number per box, number of boxes on hand, and loose items. The last three equal currently equal the total. i have a separate worksheet for tracking the issue of these items, using a dropdown menu that references the master list. I would like to some how have a calculation for the total column on the Master Inventory that reflects the current inventory levels minus any quantity of items issued on the Items Issued sheet. I have attached a copy of my sheet for review. Any help or suggestions with this would be much appreciated.

Welcome to the forum jgarcia79,

I assume you want to use the Issued column (Col G) in your table on the first sheet, then adjust your Total column so that you are showing the Net result of your stock held less the stock issued.

The immediate problem is that your Master Inventory includes a description (egRed or Orange, Grey. This is not shown on the issued sheet. Therefore, you can't complete what you want.

I think you need to give that some thought before we take the next step.

You also need to think about stock replenishment. What happens when new stock arrives - you don't appear to have allowed for that.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Any thoughts on this or a similar solution would be appreciated.

Hello jgarcia,

I have played around a little!

On the Master sheet, I have added a column (K) which combines the first three columns. This list has been named Items and this list is used for the drop down box on the Items Issued sheet. This way, you are able to pick the color etc. you want.

I changed the formula in the Total column (H) so that it includes those issued. (Issued numbers come across as negative values).

I have used the SUMIFS() formula in the [I]Issued /I] column, because I assume it is possible to issue the same product more than once. VLOOKUP type formula will only find the first issue, so will not work.

On the Items Issued sheet, I added a column for Stock Held. This will turn RED (as will the QTY column if you try to issue more units than you have.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David, this is spot on what i was looking for. I can't thank you enough. Last question. The column you added, should i just replace the item and description columns i have and use that. That way all i would need to to would be to change the cell references for the drop down menu on the items issued page? I like the way you did it better than the way i have have it setup. I currently just hid the extra columns for formatting issues,but other than that it is great.

That would probably work. I assume you are suggesting making column C be a combination of Item Name and Description.

One thing to be aware of is you will need to ensure that you don't have blank cells. (Just put a space in them). The reason for this is to prevent the dropdown box starting in the middle of the list (where is finds the first space at row 90).

Regards

David