Thread: Inventory and distribution sheet

hi all. So this may not even be possible (which I don't entirely believe), but I have one sheet which is tracking our entire inventory in rows A and B. On the second sheet is a log which tracks all of our distribution column A is the Item item name and column D is the quantity issued.

My question is this: Is there a way I can have the inventory on sheet 1 identify the item selected from the drop down in Column A sheet 2, and the quantity selected in column D sheet two and auto update the inventory number in column B sheet 1.

Ive been fighting with this for days and the finished product is needed ASAP so any and all help would be greatly appreciated.

Thank in advance!!

Originally Posted by alansidman

It would be helpful if you posted a sample spreadsheet with data and expected outcomes.

Here's a basic replica with all the actual (sensitive) data removed, Hope this helps in finding a solution.

I think I figured it out! I used the formula =SUMIF(Table1[Item],Table2[[#This Row],[Item]],Table1[Quantity Issued]) to determine how many of each item were issued and placed said formula in a new range (chart). Then using the formula =SUM(Table2[[#This Row],[Initial count]],-Table2[[#This Row],[Currently Issued]]) in the Quantity in stock column the list auto-populates any inputs from the Issue log. If you folks have a better way of doing this I'm all ears but this is definitely doing what I was hoping it would.