Vlookup

please see the example file attached

Hi Ben,

Good example. I would recommend one thing, though:

Instead of typing each employees name into the Data Validation list, use a dynamic named range. If you type the list, 1) it could be very long; 2) to change it would require you to edit the validation list and then apply that change to all cells with similar validation; and 3) could be prone to typing errors causing errors when trying to lookup the names. By using a dynamic range, you never have to worry about that.

To create a dynamic list of employee names, once they're entered onto Sheet2 column A, click Insert -> Name -> Define, type in a name (like: EmpList) and then use the following formula as the reference:

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

Then, in the first "name" cell in column F (say F2), select Data -> Validation, Allow: List, Source: =EmpList

From that point forward, whenever you add or remove names from the list on Sheet2 it will be reflected in the drop-down lists.

Hi pjoaquin,
I like your suggestion, and I can use it immediately in one of my own WB's