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? :confused:

Thanks in advance for any assistance on this!

Dylan Ross

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