Subtracting the value of one cell from another, and hve it rtain the new value

[factrat]

Need a formula or macro that will allow me enter a 1 into a cell, on sheet1, lets say B2, and subtract it from a cell H3 on sheet2. Sheet2 H3 =10. When I enter the 1 it should subtract the 1 from the 10, and make H3=9, and when I do it again, the value of H3 will be 8, and so on till it reaches 0. It is for an attendance point system.


Monday...H3's initial value being 10. Sheet2!H3=Sheet2!H3-Sheet1!B2
Tuesday...H3's initial value being 9. Sheet2!H3=Sheet2!H3-Sheet1!B2

The problem is that Sheet2!H3 will not retain the subtracted value.

I've been trying to do this for a few days, and it seems to me, it should be simple, but …..
Any help would be greatly appreciated! Thank you!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make sure that a value exists in H3 of Sheet2. Enter a value in B1 of Sheet1 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
    Sheets("Sheet2").Range("H3") = Sheets("Sheet2").Range("H3") - Target
End Sub

 

[factrat]

AWESOME! Works perfectly! Thank you! This should help me finish my project.

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make sure that a value exists in H3 of Sheet2. Enter a value in B1 of Sheet1 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
    Sheets("Sheet2").Range("H3") = Sheets("Sheet2").Range("H3") - Target
End Sub

 

[mumps]

You are very welcome.

 

[factrat]

Felt I needed to show you how I was trying to use a formula to subtract from a cell.

=IF(Sheet1!C6=Sheet4!B3,Sheet4!C3-Sheet1!G32)

The Sheet1!C6=Sheet4!B3 is checking for a name in a C6 being the typed name, and B3 being the name in the list of names. The list is B2-B56.
I was going to call the formula once for each name, ,but then that's where the issue of retaining the value came in, and you show me how to fix. Again Thank you !!

 

[mumps]

My pleasure.

 

[Peter_SSs]

I would normally use the macro code suggested, but note that this can be done with formulas (with a bit of set-up effort).

Assuming the cells are H3 on Sheet2 and B1 on Sheet1, the steps would be ..

1. In File -> Options -> Formulas -> Mark the 'Enable iterative calculation' box -> Change 'Maximum Iterations:' to 1 -> OK (Note that this setting only applies to this workbook)
2. Clear H3 and B1
3. In H3 put the formula =H3-Sheet1!B1 (The cell will show 0 for the moment)
4. In B1 enter the negative of your desired starting value ( -10 for your example). H3 should now hold the starting value of 10
5. Now, as you enter 1 (or whatever value you want) in B1, that number will be subtracted from H3 and the result displayed in H3.