If a column has a value add to another column?

Solved/Closed
Anonymous User - Updated on Sep 25, 2018 at 12:15 PM
Anonymous User - Aug 30, 2018 at 12:05 PM
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?
Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 30, 2018 at 11:23 AM
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
Aug 30, 2018 at 11:38 AM
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.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 30, 2018 at 11:47 AM
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
Aug 30, 2018 at 12:05 PM
Worked Like a Treat Thank you so much.