Salary data entry - circular reference a better way
Some sample code which assumes data is in columns A to E:
Right click on your "salary" worksheet , select "View Code" and copy/paste
code below.
Changes to columns C or D will execute relevant calculations.
HTH
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim Current_Salary As Double
Dim New_Salary As Double
Dim Proposed_Increase As Double
Dim Perc_Increase As Double
Dim Increase As Double
On Error GoTo ws_exit
If Target.Row = 1 Then Exit Sub
' If changes not in Columns C or D then exit sub
If Target.Column < 3 And Target.Column < 4 Then Exit Sub
Application.EnableEvents = False
r = Target.Row
Current_Salary = Cells(r, "B")
Select Case Target.Column
Case Is = 3 ' % increase
New_Salary = Current_Salary * (1 + Target.Value)
Proposed_Increase = New_Salary - Current_Salary
Cells(r, "D") = Proposed_Increase
Cells(r, "E") = New_Salary
Case Is = 4 ' $ increase
New_Salary = Current_Salary + Target.Value
Proposed_Increase = New_Salary - Current_Salary
Perc_Increase = Target.Value / Current_Salary
Cells(r, "C") = Perc_Increase
Cells(r, "E") = New_Salary
End Select
ws_exit:
Application.EnableEvents = True
End Sub
" wrote:
I have a spreadsheet which will be used to calculate salary
information.
Important columns a
Job Title - Current Salary - Proposed increase (%) - Proposed increase
($) - Proposed new salary
What has been requested by the client is to be able to select ether a
% or $ change to salary and have each column reflect the change. eg:
% increase updates $ increase and salary and $ increase changes %
and salary.
It is possible by introducing a circular reference but this is a _bad_
thing from all accounts. What other options would I have?
tia
W
|