If a Column has a Value add to another column? [Solved]

The4Elements 3 Posts Thursday August 30, 2018Registration date August 30, 2018 Last seen - Aug 30, 2018 at 11:05 AM - Latest reply: The4Elements 3 Posts Thursday August 30, 2018Registration date August 30, 2018 Last seen
- 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?
See more 

Your reply

4 replies

TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen - Aug 30, 2018 at 11:23 AM
0
Thank you
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 3 Posts Thursday August 30, 2018Registration date August 30, 2018 Last seen - 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.
Respond to TrowaD
TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen - Aug 30, 2018 at 11:47 AM
0
Thank you
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 3 Posts Thursday August 30, 2018Registration date August 30, 2018 Last seen - Aug 30, 2018 at 12:05 PM
Worked Like a Treat Thank you so much.
Respond to TrowaD