Vlookup

[Mr Amm]

I use a simple spread sheet at work for keeping track of items borrowed and returned from the toolroom. I would like to speed up the process of entering data by automating one of the fields, but i have no idea how to go about it. Here is the problem, i have Column F Employee names and Column G Employee ID#, at the moment i have to type in the name then look up from a list the ID #. What i would like to happen is once i have typed in the name in F and the # in G that excel will remember that those two are linked, so that when i type the name again it will automatically fill in the ID #. Can anyone help.

[Paul]

Hi MrAmm,

Your best option will probably be to create a lookup table elsewhere in your workbook, and then use VLOOKUP formulas in column G to return the ID# based on the name you enter in column F.

For example, on Sheet2, in column A list all of your employees, then in column B list their employee ID numbers. Let's assume your list goes from cell A1 down to A100 (and ID's from B1 to B100).

Back on Sheet1, in cell G2 (assuming your data starts in row 2, with headers in row 1), use the formula:

Please Login or Register  to view this content.

[protonLeah]

please see the example file attached

[Paul]

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.

[protonLeah]

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

[Mr Amm]

Thanks guys that worked great. Only problem i had was that my current spreadsheet is a little large for all the editing (11,500 entries) but i will definatly use it when i make my 2008 sheet. Looking at that number just made me think maybe i should do 6 month sheets