Salary data entry - circular reference a better way

[william.hamilton]

I have a spreadsheet which will be used to calculate salary
information.
Important columns are:
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

 

[Lars-Åke Aspelin]

I have a spreadsheet which will be used to calculate salary
information.
Important columns are:
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

I suggest you introduce two now columns, just to the right of the
Current Salary column.
The first of theses is used to enter "%" or "$", you can use Data
validation to avoid anything else to be entered.
The second is used to enter the value, i.e. the real % or $.
Now you can have the rest of you columns, Proposed increase(%),
Proposed increase ($), and Proposed new salary to all be output
column, i.e. the are not used for entering data. You may use Worksheet
protection to avoid data to be entered in these columns destroying
your formulas there.
Hope this helps.

Lars-Åke

 

[Lars-Åke Aspelin]

I suggest you introduce two now columns, just to the right of the
Current Salary column.
The first of theses is used to enter "%" or "$", you can use Data
validation to avoid anything else to be entered.
The second is used to enter the value, i.e. the real % or $.
Now you can have the rest of you columns, Proposed increase(%),
Proposed increase ($), and Proposed new salary to all be output
column, i.e. the are not used for entering data. You may use Worksheet
protection to avoid data to be entered in these columns destroying
your formulas there.
Hope this helps.

Lars-Åke

"now" should be "new" of course

 

[william.hamilton]

I suggest you introduce two now columns, just to the right of the
Current Salary column.
The first of theses is used to enter "%" or "$", you can use Data
validation to avoid anything else to be entered.
The second is used to enter the value, i.e. the real % or $.
Now you can have the rest of you columns, Proposed increase(%),
Proposed increase ($), and Proposed new salary to all be output
column, i.e. the are not used for entering data. You may use Worksheet
protection to avoid data to be entered in these columns destroying
your formulas there.
Hope this helps.

Lars-Åke

Thanks for this. How would I do a conditional formula based on $ or %
in the cell? I like the idea you have presented nice technique if I
can get it working

Would you be able to supply an example formula for checking $ or % and
calculating result?

tia

W

 

[william.hamilton]

Thanks for this. How would I do a conditional formula based on $ or %
in the cell? I like the idea you have presented nice technique if I
can get it working

Would you be able to supply an example formula for checking $ or % and
calculating result?

tia

W

Answering my own question here I have worked out along the lines of
=IF(CELL("Format",A1)="P0",A1*A2,IF(CELL("Format",A1)="C0-",A1+A2,"INVALID"))

cheers

W

 

[william.hamilton]

Answering my own question here I have worked out along the lines of
=IF(CELL("Format",A1)="P0",A1*A2,IF(CELL("Format",A1)="C0-",A1+A2,"INVALID"))

cheers

W

Making a habit of talking to myself. How do I set the format of a
cell? I have found I can format to Dollar if other cell is % but how
would I go about the alternate?
I now have:
=IF(CELL("Format",N3)="P0",DOLLAR(N3*E3),IF(CELL("Format",N3)="C0-",E3/
N3,"INVALID"))

tia

W

 

[Toppers]

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

 

[Toppers]

You would have to use VBA ... a worksheet event such as Worksheet_Change

take a look here:

http://www.cpearson.com/excel/events.htm

Come back if you need more help.

 

[Lars-Åke Aspelin]

Making a habit of talking to myself. How do I set the format of a
cell? I have found I can format to Dollar if other cell is % but how
would I go about the alternate?
I now have:
=IF(CELL("Format",N3)="P0",DOLLAR(N3*E3),IF(CELL("Format",N3)="C0-",E3/
N3,"INVALID"))

tia

W

My idea was not to use the CELL function at all.
Just write the single character, % or $, in the first of the new
columns.
Then write 3.14 or 123.45 or whatever % or $ value your are working
with in the second new column.
Then you use the IF function to calculate the three "Proposed ..."
columns based on the value, not the format, of the first column.

Example:
Column A is the Job Title
Column B is the Current Salary
Column C is the new % or Dollar indicator column
Column D is the new % or Dollar value columns
Column E is the Proposed increase (%)
Column F is the Proposed inxrease ($)
Columnt G is the Proposed new salary

Formulas:
In cell E2 enter the following
=IF(C2="%", D2, D2/B2*100)

In cell F2 enter the following
=IF(C2="$", D2, B2*(1+D2/100))

In Cell G2 enter the following
=B2+F2

Lars-Åke

 

[William Hamilton]

I have a spreadsheet which will be used to calculate salary
information.
Important columns are:
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

Thanks for everyones input - I ended up basing my solution on the code
provided by Toppers - A special thanks to you Topper.

I had done some VBA within Access apps but not much within Excel -
events are great!

W