Selected Answer

Hi.

Thanks for attaching the file. Please see my modified version attached...

In that, I've used the VLOOKUP function instead of typing the cell containing the surname

E.g. in (yellow) cell C15, you had *=SUM(C15*C4). *Note that* y*ou didn't actually need the SUM function there*, **=C15*C4 *does the same thing.

I've replaced it with this:

`=C15*VLOOKUP(B15,`**$B$3:$C$5**,2,FALSE)

I've copied that to C16, C22 and C23 but it can be copied anywhere where there is a cell to the left containing a name in the range of cells in bold above.

The VLOOKUP looks for what in B15 in the left-most column (1) of the range $B$3:$C$5 (in bold above). The FALSE bit requires an exact match (so not the surname close to it) and if it finds one, it returns the value in the column indicated by the **2 **in the formula i.e. the next column, C. That then get mulitpled with the hours.

Hope this is what you want. Look in the Tutorial section and you'll find more guidance on this function (it has its quirks!)

*REVISION 1: **You can make life even easier for yourself by using data validation. I've attached a revised sheet where you don't need to type the overtime names (and risk an error for the VLOOKUP). Click in B27 and a down arrow will appear where you can just pick one of the names from the table without typing (I've cheated and added the row below the table so there's a blank option). The "real" ones B15, B16 etc, are the same and you can copy a cell to anywhere in the sheet and the validation will follow: values you can enter can only match that list. Here's a tutorial to help you understand data validation: Excel Data Validation - Limit What a User Can Enter into a Cell. *

*Note that you can apply this to the answer from Variatus too (also check out the tutorials about automatically updated validation).*