# If a column has a value add to another column?

Solved/Closed
-
Anonymous User -
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?

## 2 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Anonymous User
The idea is that there is 3 Colums (Ill use the names as ref) AA ,AB & AC Each colum will have a figure in it but AA & AB can only have a maximum Value of 100 in it before having to be reset back to 0 with the 100 moving a column over. E.g. if AA = 100 Then that 100 would move over to AB and = 1 while AA Would go to 0 and so on over to AC.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Anonymous User
Worked Like a Treat Thank you so much.