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

Report
Posts
3
Registration date
Thursday August 30, 2018
Status
Member
Last seen
August 30, 2018
-
The4Elements
Posts
3
Registration date
Thursday August 30, 2018
Status
Member
Last seen
August 30, 2018
-
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
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
The4Elements
Posts
3
Registration date
Thursday August 30, 2018
Status
Member
Last seen
August 30, 2018

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
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
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
The4Elements
Posts
3
Registration date
Thursday August 30, 2018
Status
Member
Last seen
August 30, 2018

Worked Like a Treat Thank you so much.