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