Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick lookup for data
I have a worksheet that has row headers with Employee numbers, salary, names
etc. I want to create a quick lookup that allows me to type in the employee number and the resulting query will bring back all the data about that specific employee. A quick lookup per employee. Can anyone help on what function or how to do this? I am using Excel 2007 Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quick lookup for data
You could on a seperate sheet, set up such a "form"
You would have an input cell, where you would either type in the number of the employee, or select it from a dropdown box. This would then populate the rest of the form. The major requirement would be that your database of employees must be sorted by employee number, and that this number must be the leftmost column of the database. Also, I prefer to name this entire range, say as Employees. (Block the data, click on Insert, Name Define, and give it a name) Let's say this range is from A2:K50 A=Nr, B=Surname, C=Initials, D=Name, F=Position and so on Type in the number: Say you use cell C5 as input. Where you want information, use a VLOOKUP formula. VLOOKUP needs to know how many columns to the right it will find the information, based on the number in C5 Given the above example, you could use =IF($C$5="","",VLOOKUP($C$5,Employees,2,0) to extract the person's surname =IF($C$%="","",VLOOKUP($C$5,Employees,3,0) would give the initials, and so forth. To save on typing, enter one formula, copy it to the other locations, and merely change the column numbers to suit. That's why I used absolute addressing. To look up in a dropdown box: Block Col A, and name it CoyNr or something. In C5, click Data, Validation, change Anything to List, and insert =CoyNr in the formula box. -- HTH Kassie Replace xxx with hotmail "Max" wrote: I have a worksheet that has row headers with Employee numbers, salary, names etc. I want to create a quick lookup that allows me to type in the employee number and the resulting query will bring back all the data about that specific employee. A quick lookup per employee. Can anyone help on what function or how to do this? I am using Excel 2007 Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation quick find | Excel Discussion (Misc queries) | |||
Excel 2002: Any quick way of transposing data ? | Excel Discussion (Misc queries) | |||
quick double-criteria lookup? | Excel Worksheet Functions | |||
quick change of data | Charts and Charting in Excel | |||
is there a quick way to put spaces in data? | Excel Discussion (Misc queries) |