# Counting formula

#### Puggwash63

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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

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``````

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

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

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

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

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).

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

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

Replies
0
Views
305
Replies
3
Views
673
Replies
1
Views
443
Replies
3
Views
4K
Replies
9
Views
217

1,203,078
Messages
6,053,405
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back