Selected Answer
PM
You could use VLOOKUP to do this (especially if combined with Data Validation and tables/ named ranges).
You didn't attach an Excel file but in the attached file you'll see two yellow cells in Sheet1. There's a drop-down list to change the name and that will cause the value in A2 to change, according to the rate. The formula in A2 could be:
=A3*VLOOKUP(A1,'Employee rates'!A1:B4,2,FALSE)
where A3 is the worked hours (the second yellow cell- change that and A2 changes too).and the bit '
Employee rates'!A1:B4 points to cells in the sheet called "Employee rates'". The VLOOKUP formula means "
lookup the name (in cell A1) in that area and when you find it,take the value from the column number 2 in that area"
In fact I've made that arrea a named table "RatesTable" and the cell A1 has the name "EmployeeName". The formula in A2 becomes:
=A3*VLOOKUP(EmployeeName,RatesTable,2,FALSE)
which is more readable. You can refer to Name Manager (under Formulas in the ribbon) to see which cells those things mean. You could also give cell A3 a name e.g. Hours to make it even easier.
Note that Data Validation (List) has been used to give the dropdown in A1 of Sheet1. Add another name below the Rates Table and you'll see that the dropdown list changes to suit.
You may frind some of Don's tutorials helpful to understand this- e.g. Vlookup with a Range of Numbers in Excel and Excel Data Validation - Limit What a User Can Enter into a Cell.
Hope this helps rather than confuses.