Hi,
We like to maintain one of our machines by the number of rolls that have passed through it. We record this on Excel and I get the operators to record X in the service clean box when the machine has been cleaned.
=24-(LOOKUP(2,1/(Table1[WO]<>""),ROW(Table1[WO]))-LOOKUP(2,1/(Table1[Service Clean]<>""),ROW(Table1[Service Clean])))
This then tells us how long until the 24 service clean(the reason for 24 at the start of the formula.) The above formula works well when the operators fill in the table down. However for some reason on the next line the operators fill the table by inserting a new line at the top. I'm getting resistance to changing that and there is some historic macros that would need to be changed so I want to avoid this at all costs.
Is there a way to change the formula so that it works in reverse. It looks for the position of the first X in the column. In the example it is cell D7. Then counts upwards to row B. So it informs me with done 5 rolls since the last clean?
Cheers,
Tom
Bookmarks