Forum Discussion
Excel issue
Hello everyone, I'm currently having an issue with a formula and I'm trying to figure out a solution for it. So I have a table that live feeds information that I'm referencing. In the table I have all of the machines that I'm in charge of, some machines make parts and other machines use those parts to create a finished product. I have a working formula for everything except for 1 machine that makes multiple different finished products. I'm trying to keep track of how many parts we have at any given point throughout the shift. The formula I'm using is =SUM((IFERROR(INDEX([this is where I pull the number of parts we had at the end of last shift], 0)),SUMIF(this adds all of the parts that have been made at any given point during the shift)-SUM((IFERROR(VLOOKUP(looking for one of the finished products that we make,0)),(IFERROR(VLOOKUP(looking for the other product that it makes),0))). This formula works until we change the machine that makes the finished product to make a different product because the reference table only keeps track of total count. For example, we start the day with 40 of part A and 10 of part B, we make another 20 of part A during the shift and use all 60 to make 60 of product AC which should leave us at 0 for the inventory. We switch over to product BC. We've ran another 15 part B on shift so we have 25 available part B in inventory, but since the machine says 60 products have been made already the cell will say we're at -35 part B and still have 60 part A because the reference cell no longer shows the machine as running product AC. Does anyone know a way to fix this?
- It is really hard to know without a sample sheet to understand what you have and working with. From what I gather is there is some table with totals and a reference cell about which part is being made by the machine at this time and when that changes it messes things up. My suggestion would be to create a column that calculates the totals for each part independent of what the machine is presently set to.
- The issue is that this page is the only way the get real-time numbers on production, if that machine changes what it's making at any point it becomes inaccurate because instead of seeing that there were already 40 things of one type made, and the first unit that the machine kicks out of a different type counting for 1, excel just sees that as being 41 and removes 41 parts from the inventory.
- so somehow the excel sheet is reading information from the machine like # cycles but the machine doesn't know what those cycles are making? but somehow you want excel to know? You have to have more information and/or user intervention. That could be copying data over or running a macro or something. Again, very hard to help when I don't know exactly what is in the sheet and what info excel gets and such.
