Update two cells by changing another

[teriboyd1]

In column A, I have salary. Column B has increase %, column C has
increase amount, column D has total new salary. I need a macro that
allows me to update column B, C, or D by entering infomation in either
of those. So, if someone enters an increase of 5% in column B on a
10,000 salary, then column C should change to $500 and column D should
change to 10,500. Or, if they enter 10,500 in column D, the other two
cells should change, etc.

Can anyone help with this?
Thanks.

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nCol As Long
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("B21000")) Is Nothing Then
On Error GoTo ErrorOrExit
Application.EnableEvents = False
nCol = .Column
With Cells(.Row, 1).Resize(1, 4)
Select Case nCol
Case 2
.Cells(3).Value = .Cells(1).Value * .Cells(2).Value
.Cells(4).Value = .Cells(1).Value + .Cells(3).Value
Case 3
.Cells(2).Value = .Cells(3).Value / .Cells(1).Value
.Cells(4).Value = .Cells(1).Value + .Cells(3).Value
Case 4
.Cells(3).Value = .Cells(4).Value - .Cells(1).Value
.Cells(2).Value = .Cells(3).Value / .Cells(1).Value
End Select
End With
ErrorOrExit:
Application.EnableEvents = True
End If
End With
End Sub

 

[Edmund]

You can do this with 2 columns or rows in Excel. One to hold the input of
B, C, D and the other to do the conditional calculations. An input rule
would have to be followed that that only one cell in the input set (B,C,D)
has a value at any given time; meaning that the user will have to clear the
values in the set before entering a new value. I would set it up as
follows.

Headings
A1: Salary
A2: % Increase
A3: Increase Amount
A4: New Total

Highlight cells B2, B3, B4, C1; these are the input cells. Only one B cell
can have a value at any one time.
Format all cells Comma Style (the comma button on the Formatting toolbar)
except cells B2 and C2 which should be formatted Percent Style (the percent
button).

B4: =IF(COUNT(B2:B4)>1, "Too many inputs", "")

C1: 100000
C2: =IF(NOT(ISBLANK(B2)),B2,IF(NOT(ISBLANK(B3)), B3/C1, B4/C1-1))
C3: =IF(NOT(ISBLANK(B3)),B3,IF(NOT(ISBLANK(B2)),B2*C1,B4-C1))
C4: =IF(NOT(ISBLANK(B4)),B4,IF(NOT(ISBLANK(B2)),(1+B2)*C1,B3+C1))

All done. You can change any off the highlighted cells but, again, be sure
that only one of the B cells has a value at any one time.

E