Counting formula

[Puggwash63]

Hi,

I have figures in certain cells (A1 = 5000, B1 = 3500 etc) as example

In Column C running down (C1, C2, C3 and so on) I input in each cell 150 and it reduces A1 (5000) each time (5000 - 150 = 4850 and so on till A1 = '0'.

Can you please help me so that once Column C has been entered with the 150's and A1 then = 0 or shows a minus, if I add another 150 to Column C, it will start to reduce B1 (3500) figure or when there is a minus in A1 etc, it will reduce B1 total.

I hope this made sense

Many thanks

Pugg

 

[venkat1926]

see whether this EVENT CODE helps you


right click the tab of the sheet and click view code

in the window that comes up paste this event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
Application.EnableEvents = False
If Target = 150 Then
If Range("A1") > 0 Then
Range("a1") = Range("a1") - Target
Else




Range("b1") = Range("b1") - Target
End If
End If
If Range("a1") < 0 And Range("B1") < 0 Then
Range("a1") = 0: Range("B1") = 0
GoTo eexit
End If


eexit:
MsgBox "over"
Application.EnableEvents = True




End Sub

 

[Puggwash63]

Thanks for the reply.

It does not seem to work.

I have pasted the code and checked when entering 150 in Column 'C', A1 or B1 does not change.

Any ideas

Pugg

 

[Puggwash63]

Hi,

I got it to work but would like to know:
If I added the 150 to the cells in Column 'C' and made a mistake by entering too many.
How do I remove the 150 from the cell and the A1 or B1 figure increases by the amount removed.

Pugg

 

[Special-K99]

Here's a way with formulas

in A1
=5000-150*IF(ROW(OFFSET(C1,COUNTA(C1:C1000)-1,0)) > 34,34,ROW(OFFSET(C1,COUNTA(C1:C1000)-1,0)))
in B1
=3500-150*IF(ROW(OFFSET(C1,COUNTA(C1:C1000)-1,0)) < 35,0,ROW(OFFSET(C1,COUNTA(C1:C1000)-1,0))-34)

ROW(OFFSET(C1,COUNTA(C1:C1000)-1,0))
determines the number of 150's in column C

UPDATE: This should also solve your "removing 150 if entered by mistake" problem

 

[Puggwash63]

Hi, that works but when I add the formula to A1 and B1 it comes up as #REF! and does not show the number.

Also, if I need to add a different figure instead of 150 to Column 'C', what formula code do I need adding to make that work

Pugg

 

[Special-K99]

If there's nothing in C1 it will produce #REF!
Once you start entering numbers into column C the #REF! error will disappear.
In the two formulas just change the 150 to something else.
Personally I'd change the 150 to D1 and enter 150 in D1
Bear in mind you won't be able to mix numbers, ie start subtracting 150 each time then part way through change it to 200 for example.
That will produce the wrong results as it's a calculation based on one number 150 (or whatever you put in D1).

 

[Puggwash63]

Is there any way this can be done to show what A1 and what B1 number is, as the Team generating the sheet will need to know what the figures are when entering their confirmed.
What formula would I need to create if the cells change with different numbers when added

Pugg

 

[Puggwash63]

Does anyone have any further update to my quest in having the sheet auto work.

The help so far has worked well but I need a further update to the replies I have given, so the worksheet is sorted

Pugg