Inventory Tracking - Formula Needed
Our take
Picture 1 is my Transactions sheet, picture 2 is my Order sheet ETA: Excel 365 in Chrome browser. Screenshots are from Excel iPhone app - but all work is done via desktop.
I'm trying to pull a number from column G if the transaction ID in column I (picture 2) matches the TransID in column B (picture 1). I also want to make the number negative.
Example - T8 is an order of 5 clusters, each cluster is made of 3 different balloons plus a weight, hence the multiple T8 TransID, 1 per item. My sheet already uses the Quantity column in picture 2 to update an inventory sheet - that's why I need to make the number negative.
Is there a formula that can do this for me or am I completely overthinking this whole thing?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Inventory Audit using XLookup w/ multiple criteriaI've been working on a spreadsheet to keep track of some inventory. It has how many units entered and exited each day, for each item. Using this information, I have then compare it to the list of in/out inventory sent to us by a third party, basically to confirm that our numbers and their numbers match. This is where I'm struggling. I can't seem to find an efficient way of comparing these numbers that works. I've tried XLookup and Nested formulas, Index Match, etc and nothing seems to be working. I need the "total" numbers for each day from the first image to go into the "Double Check" column on the second image, matched for the item and date. I'm not sure if it's an issue because some is horizontal and some is vertical? Would love if anyone had a solution or better method to do this, thanks! Edit for more info: the image with the inventory, the "in" and "out" columns both pull data from a separate sheet that has all of the relevant information and then the "total" column is a formula that just sums the in/out with the previous days' inventory count. https://preview.redd.it/0r74aaf1nang1.png?width=1225&format=png&auto=webp&s=b024c5dad6c53315bfc91199aed1585e74512cba https://preview.redd.it/z799p9f1nang1.png?width=535&format=png&auto=webp&s=2df6c58a1f26c43d0d0b37801eb5d03cc05bdbfd submitted by /u/Fluffy-Reaction6723 [link] [comments]
- Simple Inventory In-Out Sheethttps://preview.redd.it/khli3c681ang1.png?width=1235&format=png&auto=webp&s=9fc6254e73d91e8684d1c5d7de323b78a2698b4a Where have I gone wrong here? How can I make the balance of the eggs from this example read "1"? I was using a different function previously - ex.: =SUMIF(B6:B200,H6,C6:C200)-SUMIF(E6:E200,H6,F6:F200) The problem I had was that the range was static and would move as I added rows so I would have to manually move the top of the range up to the first row and move the bottom of the range down. I don't use excel much I just need this simple balance sheet for keeping track of inventory, ideally minimizing the amout of manual labour I have to put into it (lol) any help would be greatly appreciated. submitted by /u/sikdoo223 [link] [comments]