200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I'm trying to rememeber if it is possible to create a formula that would enter the Rate of Pay when a surname is enter into a field.
Is it possible when I add name under the day worked for the rate of pay to be associated with the name so that it imports the rate of pay instead of me manually doing it for each day?
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 you 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)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).
The attached workbook is an upgraded copy of the one you posted. In it I changed your OT_ROP table into a real table. That's easy to do. Just select the entire table, select Insert > Table from the Ribbon and confirm that your table has headers in the dialog box that opens. After doing this, the table's default name will be displayed at the top left of the screen. Change it to OT_ROP.
The advantage of doing this is that you can have this table anywhere in your workbook and it will expand automatically as you add more names. Mind, however, that for the use of VLOOKUP in the following the employee's name must be in the first column. (If this becomes inconvient over time you can probably se XLOOKUP later if you have a new version of Excel.)
The lookup function is in E15. From there you can copy it down to E16 and E22:E23.
=VLOOKUP($B15,OT_ROP,2,FALSE)*$C15As you see, the formula looks up the name in B15 in the OT_ROP range above created and returns the value from the second column of that range.
What if the name in B15 isn't found? I first thought of adding an error handler in the formula but then decided against that. In fact, I think, you will want to be alerted if there is an error - as might be caused by mis-spelling of the name in B15.