Subtracting from different worksheets & finding the "last" match in column

Good day to all,
I am new to Excel formulas/functions and am struggling to make my inventory system work (all while trying to learn Excel functions). Hoping someone can help. I will try to explain the attached workbook and what I need it to do:


The Workbook (5 worksheets):
1) Slug Inventory: This worksheet will contain the “real-time” inventory level by Slug #, as well as its MIN level.
2) Inventory Rec.: When we receive shipments of the Slugs we order, Shipping will input the inventory received into a Google Form, which downloads into this worksheet.
3) Slug Usage: When we use Slug inventory, team members will complete a different Google Form to track the inventory that was pulled, as well as the unused inventory that is put back on the shelf. This worksheet retains the data from that form.
4) Cell Locos: This worksheet needs to show which cell location each Slug # (and its quantity) is located at in our warehouse.
5) Workbook Functions: This is just a worksheet that I created to help and to keep track of functions used in this workbook.


Functions I need help with:
1) Subtracting from Different Worksheets: I cannot figure out how to subtract the “Slug Usage” in worksheet 3 from the “Slug Inventory” in worksheet 1. The “Slug Usage” worksheet will be an ongoing list with daily entries. I need Excel to first find the matching Slug# in worksheet 1 (Slug Inventory), and then each time a matching slug number is entered into the (ongoing) worksheet, it will subtract from the inventory level in Worksheet 1.

2) Finding last match in a column: Worksheet 4, “Cell Locos” – team members need to view this table so that they know where to find the slug inventory. This table needs to pull from worksheet 2 (Inventory Rec.). When Shipping enters the “inventory received” into the Google form, they will also assign a cell location in which they are storing the slug inventory. I am using Index & Match functions, but of course it is only picking up the first matching value for the cell location ID, and I need it to pull the last matching value, as this worksheet will also be an ongoing worksheet with multiple entries (i.e. location cell CI-1 will have different slug inventory going in and out of it).


I just joined the Forum yesterday as I feel it will be a significant help to me. I hope this all makes sense. If you need further explanation, please let me know. In advance, thank you!

-Michelle

Hi ChemistB,

The first worksheet (Slug Inventory) is "supposed" to be my "Initial Inventory." Maybe I'm missing a step? So, Slug Inventory (wksht1) will be used to enter the initial count of current inventory when we get this system up and rolling.

From that point on, the "Inventory Rec." (wksht2) will add inventory to the "initial inventory" into "Slug Inventory" (wksht 1). The Inventory Rec. worksheet will be an ongoing list as we receive orders that will continue to add (by slug #).

And then...the "Slug Usage" (wksht3) will first "subtract" (or so I need it to) from the initial inventory (Slug Inventory, wksht1) when the inventory is removed from the shelf, as well as "add" unused inventory back into the initial inventory on wksht1.

I feel like I'm talking in circles. I hope this clarifies? Please let me know if you need more information. Thank you so much for trying to help me!

ChemistB - THANK YOU SO MUCH!! At first it didn't work and only showed 10,000 in inventory for the first slug number (the # I've been testing throughout each worksheet). But I kept reading your explanation (in English) trying to figure it all out, and I realized that the Inventory Rec. columns needed to be adjusted from A-B to B-C - and it worked! I continued testing it - and it flows so smoothly! I have been trying to figure this out for 3 days now and I cannot thank you enough!!

Okay, so do you by chance know what I need to add to my Index/Match formula in the "Cell Locos" table worksheet so that it will read the "last" match in the (ongoing) spreadsheet? I guess I should have created two posts for both issues, as this is partially "solved." Thank you so much!!

Both issues SOLVED!

ChemistB...it worked! I cannot even begin to express how grateful I am! I want to be just like you some day!!

I have been making this all so much more complicated with multiple formulas, helper columns, etc. - where you combined most of it into one. I have been switching your formulas up in different cells trying to make sure I understand exactly what you did - and it is really helping me - a lot.

I am not seeing where I can indicate this thread has been solved, other than writing it above in the text. Again, ChemistB, thank you so very much!

Okay - I set it as solved.

I was one happy camper when I was able to change the things needed within your formula (as that is huge for me). I still would not be able to write your formulas from scratch, but I'm going to keep playing around with them and figuring them out in English. Oh, I also wanted to thank you for the link on Data Validation. I was going to download the slug #s into the Google form to avoid the errors you referenced, but going to check out the data validation instead. Thank you (also) for that heads up. And you did more than help - you actually made it so that I can RELAX for the weekend! Thank you so much!!

ChemistB,

I have another issue with one of the formulas you helped me with...the =INDEX'Slug Inventory'!$A$2:$A$100, MATCH(2, 1/('Slug Inventory'!$D$2:$D$100=A1))). This function worked perfectly when the Cell Location Table was in the same workbook as the worksheet I'm pulling data matches from. However, I had to move the table to a separate workbook, so that team members would not have access to the other sheets in the workbook. I am using Google Sheets, and am very confused. When searching different topics, it kept leading me to "Google Keys," with IMPORTRANGE. I found the correct "keys," but still nothing is working. So I have made multiple attempts to alter your wonderful formula to pull instead from the separate workbook, (while matching exact criteria and pulling from the "last" occurrence of the match in the column), but I keep getting errors. I have no idea how these threads work, but am hoping that you are notified with this post and that you can help me. Thank you in advance, ChemistB!