SOLVED

Keep number from changing after setting cell back to zero

Contributor

3 colums

1st column is the starting total

2nd column is the amount subtracted from the 1st column

3rd column is the new total by subtracting the 1st and 2nd columns
Question:

After subtracting the amount from the second column and thus changing the amount in the 3rd column, how would I go about clearing out the second column without the third column's number changing until I add a new number into the second column?

 

Example:

1st column: 100
second column: 10 ( subtracting 10 from 100)
3rd column: 90 ( remaining balance)

 

Now. I would like to "zero out" the second column while keeping the 3rd column as is
1st column: 100
second column: 0
3rd column: 90 ( This number doesn't change when you use "zero" in the second column)

 

AND THEN be able to add a new number to the second column and have that number reflect a change in the 3rd column
1st column: 100
second column: 30
3rd column: 60 

 

Zero out the 2nd column again yet keep the third column as 60 until I add a different number again to the 2nd column.

 

The way it woks now is if I put 10 ( as an example) in the second column, it changes the 3rd column to 90. BUT if I change the second column back to zero it changes the 3rd column back to 100. I want to keep it at 90 until I replace the zero with a different number.

Does that make sense?

 

 

 

16 Replies

Hello,

 

Use the manual calculation mode so that any change won't reflect until you press F9.

To turn this mode on go to Formulas (Ribbon tab) >> Calculation >> Calculation Options >> Manual.

 

Also, you can clear the below check box to keep the value as it is after saving and closing the workbook.

 

Manual Calculation.png

 

 

Hope that helps

Haytham

Thank you so much. However. It doesn't seem to work quit right.

If I start with 100 and subtract 10 and hit f9 the total does change.

I can then change the amount back from 10 to 0 and save it. The amount stays at 90-good.

If I THEN change the 0 back to 10, hit f9 then it SHOULD go to 80 but it doesn't. It stays at 90

If I put in 20 THEN it goes to 80. 
I need the number to continually go down.


 

Hi,

 

Sorry for the late reply!

 

Now I know what exactly you want!
But I don't think that what you asking for is possible using native Excel!
It requires an unusual solution to remember all the values you entered in the second column, and then subtract them from the amount.
best response confirmed by Jamil Mohammad (Super Contributor)
Solution

hopr37

 

This can be done with few lines of VBA code which i wrote below. please see the attached workbook.

 

 

 

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2", Range("B" & Rows.Count).End(xlUp))) Is Nothing Then
Application.EnableEvents = False
If Target > 0 Then
Target.Offset(, 1).Value2 = Target.Offset(, -1).Value2 - Target.Value2
End If
End If
Application.EnableEvents = True
End Sub

 

Hi Jamil,

 

I don't think that this solution is what he wants!

 

I understood from his comment that he wanted the subtraction to be cumulative and to remember all the values he entered into the cell!


If he entered 10 into cell B2 and deleted it or replaced it with 0, then returned another day and entered a new value in the cell, he wants to subtract the sum of both the new value and the old value he had deleted before!

 

You need to add some code that stores all the values that he has entered into the cell somewhere and records it, then retrieves them and subtracts from the total!

 

That's why I called the solution that needs an unusual solution!

Such a solution needs time and effort!

Hi Haytham,

Thanks for pointing that. If OP really wants to do that way then, OP can use this code below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2", Range("B" & Rows.Count).End(xlUp))) Is Nothing Then
Application.EnableEvents = False
If Target > 0 Then
Target.Offset(, 1).Value2 = Target.Offset(, 1).Value2 - Target.Value2
End If
End If
Application.EnableEvents = True
End Sub

Thanks for the replies guys. Sorry, I've been away for the holiday weekend. I'll check this out as soon as I can. Much appreciated.

Jamil: Thank you so much. It works exactly as I wanted it to. It's very much appreciated

You are welcome. Thanks for the feedback.

Jamil: one question.
I'm changing some values to see how everything works.

The only thing I can't figure out is what value2 means?

I've changed this to value1 and value3 and they both give me an error.

How does value2 figure into this? I'm "assuming" value2 is the next cell after the target cell?

Range/Cell/target . value2 Returns or sets the cell value.

Range/Cell .Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####

Range/Cell .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)

Range/Cell .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.

Detailed Explanation from by my friend Charles https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/

 

 

the next adjacent cell value targeted using target.offset 

Great. Thank You for the explanation

Can you help me with an excel problem please?

Hey Jamil, quick question hopefully you can help with on the last trouble you solved for me. ( This is by no means an emergency or that important that I can't work around it)
I've noticed if I copy the rows in column "C" ( I grab the first couple of numbers which for the moment are zero's and copy them all the way down column "C" by dragging them, I get an error message. 
Run-time error 13:

type mismatch


If I look at the formula then this is highlighted in yellow:
"If Target > 0 Then"


Here is the formula in case you forgot:

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4", Range("C" & Rows.Count).End(xlUp))) Is Nothing Then
Application.EnableEvents = False
If Target > 0 Then
Target.Offset(, 1).Value2 = Target.Offset(, 1).Value2 - Target.Value2
End If
End If
Application.EnableEvents = True
End Sub