Matching different rows with Changing criteria

[codemancode]

Good morning all!

I have a problem I've been wracking my brain wit for days now. I will upload a screen capture so that i can explain myself better than the title.

Capture.JPG

basically what you can see here is that i have made a table that has the current date, and the times of particular loads I am going to ship out. The part numbers in column D repeat because they get sent out on several loads a day. To the right is an an example of a part number and its total inventory at that moment. I've been trying to take that total number, and subtract from it in column F to show the remaining inventory of that part (where the xx are). my problem is that every time i refresh the ODBC query the sheet changes. Lines disappear as loads go out, different part numbers pop up with different times so no row will ever be the same after a refresh. The exception will be column F will have a static list of ALL part numbers, and their total quantity. Only the "total inv." in column H will change as we ship or make more parts. I have filled up the entire formula bar using SUMIFS and IF and MATCH and chaining IFERROR together and i cannot get it to be dynamic in any way. Any and all help here is greatly appreciated!!!

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[codemancode]

Thanks John,

I have uploaded an example book with a before and after. Obviously the data in column L was done manually because I dont have a formula for it yet. My problem is that i not only need to make a formula to do that, but to do it manually. "diamond" will come up in different cells depending on if it is going out that day or
how many times it goes out. so diamond starts at 500 and i ship 43, so it should show 457 left in column L. And so on.

[codemancode]

I can use the formula =IF($J10=$N2,$O2-$K10,"") in cell L10 and it works, but it only works that one time, for that specific name (diamond). If i refresh the page and J10 is garnet instead of diamond, it wont work because it is pointing at N2 which is diamond so it will just display 0. it is confounding.

[JohnTopley]

In L4

=IFERROR(INDEX($O$2:$O$3,MATCH($J4,$N$2:$N$3,0))-SUMIF($J$4:J4,J4,$K$4:K4),K4)

Copy down

If no match i.e not in your table in N:O, the result is the value in K.

If you want result to be blank then

=IFERROR(INDEX($O$2:$O$3,MATCH($J4,$N$2:$N$3,0))-SUMIF($J$4:J4,J4,$K$4:K4),"")

[codemancode]

Thank you John!

I hate how once you see a solution its obvious that it is the perfect fit haha. i am going to have to ready up on the MATCH and INDEX pairing that seems to be pretty powerful. I would never have thought of it.

[JohnTopley]

All part of a (continuing) learning curve: we have all been there at some point.

Glad to have helped.