Can Excel remember a count?

[vvilko]

This is a weird one. I cant find anything online to help me out. so I'm wondering if anyone on here knows.

I need to be able to somehow save a count value.
So below is a screenshot of my data.

The grey bar (Line 30) counts the sum of the above column. (=SUM K4:K29)
Line 31 is a manually entered cell after we do a stock count on the products.
ignore line 32 & 33.
Line 34 uses the value in Line 31 and minuses by Line 30 (i.e. K31-K30)

the cell will turn red if we need to create more products to keep up stock levels.

So when I input my customer orders, it calculates my total products and alters my free stock (line 34). which is what it's supposed to do. Below.

However, When the customer order is complete, we clear the figures from J to AH. this obviously recounts my data and updates Line 34 to show the incorrect stock.

So, as shown above, the stock for LD2 (Column P) has reverted back to it's original figure. and therefore we wont know that we need to make more to replenish our stocks.
I'm wondering if it is possible to count the stock as before, and remember what it has counted almost like a snapshot.

I hope someone has an idea on this, because I'm stumped!!

thanks in advance,
vvilko.

 

[Joe4]

I'm wondering if it is possible to count the stock as before, and remember what it has counted almost like a snapshot.

The way I see it, you have two options:
1. Copy/type that number in manually.
2. Have VBA copy/type that number in manually (or save off to some unused cell).

Maybe you could make a VBA "Clear Figures" routine that copies the number you need while clearing all the other cells, so it serves multiple purposes at once.