Auto Populate Cells

[shanealbrandt]

Hi, I have two columns in my spreadsheet - one that lists employeeID and the other lists Employee Name. I am creating a drop down lists so employees can select their manager. I would like for Excel to auto populate the manager's employeeID when someone selects their name.

Can someone assist?

The columns look like this:
ColumnA (Name)
Smith, Jane
Smith, John
Smith, Michael

ColumnB (EmplID)
123456
123457
123458

I would like that if someone selects Jane Smith, the next cell is auto populated with 123456 - Jane's employee ID.

 

[pbt]

You need to make those ranges Absolute.

=INDEX($E$2:$E$4276,MATCH(L2,$F$2:$F$4276,0),0)

A quick way to do this is place your cursor in the formular bar on say E2, and hit your F4 key. Do this for all you E's and F's.

Then you can copy down.

Harry

EDIT: for got the L2. I htink you can figure that out.

 

[shanealbrandt]

Wow guys, thanks so much for all your help! I've learned a lot. I still have the problem of the "#NA" showing before a name is selected. That is the only thing I have left to fix. So if I have this formula:

"=INDEX($E$2:$E$4276,MATCH($L2,$F$2:$F$4276,0),0)"

already in a cell, how would I change that formula to show blank unless a name is selected?

Then I am done!

 

[Smitty]

How about:

=IF(L2="","",INDEX($E$2:$E$4276,MATCH($L2,$F$2:$F$4276,0),0))

 

[shanealbrandt]

Perfect!! That is exactly what I was looking for. Thank you everyone very, very much for your help!!

-S
(Denver, CO-USA)

 

[blincoln]

I just read through this thread and there is a lot of great information here. Thanks!

My question is, how would i take it to the next step, which is populating multiple cells... To continue the example being used, if there was another column of data next to the employee numbers (lets say tenure), how could you get that to populate in the cell next to the employee number that is populated with VLOOKUP?

I hope this makes sense...

 

[Smitty]

Welcome to the Board!

If you're using VLOOKUP, you can move to the right, but adjusting the column argument. =VLOOKUP(Lookup Item,Look in Range,Column Reference,True/False).

Here's a good VLOOKUP tutorial.

Hope that helps,