In microsoft excel, how can i make a number pop-up on the next column next to a column with a name?

Question

At work everyone has an employee number. We use excel to log in information, so what I want to know is when I type an employees name on a column, how can I make the number corresponding to that empoyee pop -up on the next coumn once Ive typed their name so I don't have to type the name and the employee number seperately, it will just automatically come up when the name is typed, not next to the name but on the NEXT column. Is there some kind of link or attachement I need to do? Please explain step by step if you know how.

Answer 1

Unfortunately, there is no easy way I have found to do this. However, using the formula below as a template, you enter the last names of all employees, then their employee number. you can add as many as you need to. Just remember to add a CLOSED parentasee to the end of the equation for each new name added. As long as you follow the repetative format below, it will work.

=IF(A1="harry","12345",IF(A1="Drake","98765",IF(A1="Nelson", "034")))

To get it to work in Excel - lets say that column A is your last name box, and column D is where you want the Employee Number to appear, simply type your finished formula (as above) into cell D1, then enter. Select D1 again with you cursor, and you will see a small black box appear in the lower right corner of the cell. Click and drag on this down the page for as many employees as you have.

Now, whatever row under column A you enter a name, their Employee number will appear on the same row under column D.

Good luck!

Answer 2

1

Answer 3

Excel has a formula for this type of requirement of yours. It is called vlookup - meaning VERTICAL lookup - in your case you want Excel to LOOK UP what the employee number is for the name you typed in. Here is what you do:

In a separate column (which you can hide later), or even another sheet in the workbook, type a list of all the names of all your employees whose numbers you want to pop up. Make sure the list is alphabetically sorted from A to Z.

Now, next to these names, type the corresponding employee numbers. You now have two columns with information. This is where Excel will Vertically (down the columns) Look for your information. Pretend that you typed the names and numbers in d5:e25. This will serve as the master lookup table,and you can always add new names and numbers here, or change the information as required.

Go back to where you want to type an employee's name and have the number pop up next door. Pretend that you are going to type the name in A10. So you want the employee number to pop up in B10 right? First type a name (that you know is on your list in d5:e25) in cell A10. Go to B10 and enter the following formula, changing the cell references to match your own worksheet:

=vlookup(A10,$d$5:$e$25,2) Copy if you need to. The dollar sign ensure that Excel keeps looking in the same area only to match the name with the number.

What does the formula mean? Consider this:
=vlookup(lookup what?,look where?,and show which column in the table?)

So in this example, lookup A10 (the name), look for the name in d5:e25, and show me the number that sits in the second column of my list.

You can add sophistication by having Excel offer you a dropdown list of employee name, which will eliminate spelling error and typing, and you can have Excel cater for possible fake matches like Leon matching Leonard or Leonardo. To stop this, simply add " ,0 " to the formula, that will force Excel to make EXACT matches only. So the the formula will look like this:

=vlookup(A10,$d$5:$e$25,2,0)


Have fun :)

Answer 4

You have to do the following...
1.have a worksheet, say sheet2 listed the names(A2 to A9) and respective id#(B2 to B9), then
2.on the entry sheet, say sheet1 enter in column A all the names and column B the following formula
=INDIRECT("sheet2!"&ADDRESS(MATCH(A2,Sheet2!A$2:A$9,0)+1,2))
for more details and real excel sample, download this file
http://www.freewebs.com/swhtam/Book1.xls

if you can't see the full formula above, hover your cursor above the formula, the hidden part will show.