On Mar 28, 8:57 am, wrote:
On Mar 25, 11:07 pm, Lars-Åke Aspelin
wrote:



On 25 Mar 2007 01:14:38 -0700, 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

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

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