If a column has a value add to another column?Solved/Closed

Question

Heya, i hope someone can help me with this. I currently have a very large formula sheet made up so i can input a value and add it too my total along with a section to take away from this value to make a sort of banking system. The only problem i have is that the total numbers are split up into Three separate columns. 

Each columns besides the final one has a cap of 100. Is it possible to make a formula that would check if E.g. (AA =100, -100 & + 1 to AB) So that no matter how many 100s go into AA, Ab will allways add 1 to its Value?

TrowaD · Answer

Hi The4Elements,

So for example column AA = empty and AB1 = -100
Now for each value of 100 you enter anywhere in column AA, you want to add 1 to AB1?

The formula to use would be in AB1:
=-100+COUNTIF(A:A,100)

Not sure though if I understand you correctly.

Best regards,
Trowa

TrowaD · Answer

Hi The4Elements,

For that we have to use VBA.

Right-click your sheets tab and select 'View code'.

Paste the code below in the big white field:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns("AA"), Target) Is Nothing Then
    If Target.Value = 100 Then
        Target.Offset(0, 1) = Target.Offset(0, 1) + 1
        Target.Value = 0
    End If
End If

If Not Intersect(Columns("AB"), Target) Is Nothing Then
    If Target.Value = 100 Then
        Target.Offset(0, 1) = Target.Offset(0, 1) + 1
        Target.Value = 0
    End If
End If

End Sub

You can close the VBA window now and start testing the code.

Does it perform as desired?

Best regards,
Trowa

If a column has a value add to another column?

2 responses

Similar discussions

Subscribe To Our Newsletter!