Dynamic Cells - Can this be done?

[Dylan Ross]

I've got a spreadsheet that has one column for someone's current salary, one for their annual increase, one for the percentage, and one for the resulting "new salary".

Current Increase Amt Increase % New Salary
EX: 140,000 15,000 11% 155,000


I'm trying to make these cells function interchangeably, meaning that if someone prefers to enter an increase amount, the percentage will calculate as will the new salary. Alternatively, if someone prefers entering the %, the increase amount will calculate as will the new salary.

Does this involve making extra columns behind the scenes, or can the cells that are showing the data actually have formulas in them as well? Either way, how is it done?

Thanks in advance for any assistance on this!

Dylan Ross

 

[chergh]

For something like this I think you would need to use VBA, you might be able to do it without VBA but it is a lot easier to do with vba. The following code would probably do it, I've not tested it much but after a very quick test it seems to work.

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Left(Target.Address(rowabsolute:=False, columnabsolute:=False), 1)

    Case "B"
        If IsNumeric(Target.Value) = True And Target.Value <> "" Then
            
           Application.EnableEvents = False
           
           Range("C" & Target.Row) = Range("B" & Target.Row).Value / Range("A" & Target.Row).Value * 100
           Range("D" & Target.Row) = Range("A" & Target.Row).Value + Range("B" & Target.Row).Value
        
           Application.EnableEvents = True
           
        Else
            
            MsgBox "Enter a numeric value"
        
        End If
        
    Case "C"
        
        If IsNumeric(Target.Value) = True And Target.Value <> "" Then
            
           Application.EnableEvents = False
           
           Range("B" & Target.Row) = Range("C" & Target.Row).Value / 100 * Range("A" & Target.Row).Value
           Range("D" & Target.Row) = Range("A" & Target.Row).Value + Range("B" & Target.Row).Value
        
           Application.EnableEvents = True
           
        Else
            
            MsgBox "Enter a numeric value"
        
        End If
    
    Case "D"
    
        If IsNumeric(Target.Value) = True And Target.Value <> "" Then
            
           Application.EnableEvents = False
           
           Range("B" & Target.Row) = Range("D" & Target.Row).Value - Range("A" & Target.Row).Value
           Range("C" & Target.Row) = Range("B" & Target.Row).Value / Range("A" & Target.Row).Value * 100
        
           Application.EnableEvents = True
           
        Else
            
            MsgBox "Enter a numeric value"
        
        End If
        
End Select

End Sub