Help with stock list

I am having major problems with a spreadsheet I am trying to design. Part of it is added as an attachment. What I am trying to do is put a number in say B4 (which at present is 10) and have this subtracted from A4, the result being in D4. Now this works fine as long as I keep adding the sold item to B4. For example, if I sell another 10 items of B4 to make B4 20 then D4 reduces to 30. This is fine as long as I keep adding to the respective cell in column B. What I would like to do is put a completely fresh number in one of the cells in column B and still have the Current Stock reduce from its present number. At the moment if I clear cell B4 and put a different number in, say 5, the Current Stock list goes back up to 45. I know the reason is that the Previous Stock list doesn't change. If there is a simpler way of doing what I require then that would be great. I am hoping that someone can understand my garble.

Cheers

Brian

meddy: suppose you change B4 from 10 to 5: what would you like to see in A4 and D4?

I that think you are trying to manage a stock, right? so why not adding columns (see attached file) where each "current stock" (columns C, E, G) is the current stock level minus sold. In this way it enables you to follow changes in stock levels.

Because it may be a good soultion for your needs, this is if you are not too frustrated about having extra columns.

Otherwise I can think of a button which you'll have to click upon changing data in column B. I'll see later if I can get to it.

I hope it may help (see attached). BTW I've assumed negative stock is not allowed, so if you try to put in column B a value which is greater then column A macro won't run.

Use this version. I've added second constrain: new column B value cannot be bigger than C value (this way current stock cannot be negative either).

If I enter a number in column B (sold) then column C doesn't reduce.

Sorry, I forgot to press the button.

Feel free to change the headers of the new columns to the left.