Adjacent cells in two columns always sum to a number

[Cyclops755]

This'll be my first post up here, so here goes:

I have two columns of data, both of which contain percentage data. I'd like to have a way to force each row to always sum to 100, regardless of which column data is entered into, and that will auto-"rebalance" if the data in either of the columns is changed.
Any thoughts?

Thanks!

 

[Joe4]

Welcome to the Board!

That can be done with some VBA, specifically an Event Procedure which automatically fires whenever a value in your desired range is manually updated.

The following code is set to work on the first two columns, but you can change as needed. To insert this code where it needs to go, right-click on the Sheet name tab, select View Code, and paste this code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'   Limit to only work on columns 1 or 2
    If Target.Column = 1 Or Target.Column = 2 Then
        Application.EnableEvents = False
        If Target.Column = 1 Then Target.Offset(0, 1).Value = 1 - Target.Value
        If Target.Column = 2 Then Target.Offset(0, -1).Value = 1 - Target.Value
        Application.EnableEvents = True
    End If
    
End Sub

Then just start entering values in columns A and B and see it work!