View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ShaneDevenshire ShaneDevenshire is offline
external usenet poster
 
Posts: 2,344
Default automatically insert salary based on job title

Hi,

Suppose you want the user to type in "Labour + 10" and so on into cell A1

The create a 2 column table somewhere in your spreadsheet - in the first
column list all the entries the user may make, in the second column list the
associated salaries. Assuming that if the Labor is between Labour + 10 and
Labour + 20 you enter Labour + 10, not Labour + 12.5, you then could create
the following formula in cell B1. Let's assume your rate table runs from
D1:E15
=VLOOKUP(A1,D1:E15,2,FALSE)
This means lookup the entry in A1 in the first column of the range D1:E15
and return the value from the second column if column A is an exact match to
what the user typed.
I would avoid Labour + 10 and use Labour+10 this way it's easier to spot
typos, because Excel will not return a result if the item typed into A1 does
not match an entry in the salary table exactly. (case not important.)
--
Thanks,
Shane Devenshire


" wrote:

I have an Excel worksheet with a column indicating approx. 11
different positions (this number will probably increase). I would
like Excel to automatically insert a salary in one column based on the
job title inserted in the column prior.

e.g.

Supervisor typed in one column with automatically insert the salary in
the next column $100,000.00
Foreman typed in will automatically insert $75,000.00 in the next
column (Salary column)
Labour - $50,000.00 will insert in next column
Labour + 10 yrs will insert $55,000.00
Labour + 20 yrs will insert $60,000.00
etcetera....

I am a basic Excel user and have been trying IF as well as IF AND and
have been getting a lot of errors.

I would also be interested in a column that has a person with a Labour
title, automatically change to Labour+ 10 yrs, once the 10 years from
the start date has been reached. This would then change the salary
automatically (per above) to an increased amount.

Thank you.