I have data that corresponds to "usage" of a product. I can manually update the value as it increases and save the previous values somewhere else. However, I would like to have the previous value automatically saved as a part of a list in the same cell when it is updated. This would allow me to view how the amounts have changed and what is their effect on the end result without much effort.
Is there a viable way to do this, or should I just create lists manually for each set of old and new values?
I hope this was clear enough.
I am an excel scrub with no real training. There might be an even better way to do this than my idea.
Anything is welcome!
51 Answer
You can do it with a slight change in your table, 2 different formulas, and an understanding of anchoring.
Table changes: you don't need column C and column E become really stock/usage. So, if you are adding stock then it is positive, but usage is negative.
Formula in Column D (starting with D2 and drag down): =MAXIFS($A$1:A1,$B$1:B1,B2) This looks for the largest date based on what test name is in that row. Notice the formula ends one row above the formula row. The part that says $A$1 is known as an anchored cell. It keeps that part of the formula constant when you drag the formula down.
the Formula in column F (starting with F2 and drag down) is: =SUMIFS($E$1:E2,$B$1:B2,B2). This adds all stocking and usage from the above cells (including the row the formula is on) for that particular name (item).
If you add filtering, then the last row will always be remaining stock for a particular item.
There are other ways to solve your problem, I just solved based on how the data is presented.
3
