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]

VLOOKUP would be one option to do this.

Have a look at the Help files and post back with your attempt.

 

[shanealbrandt]

Hmm...I can usually figure out how to do a VLOOKUP, however here is what I've done.

Using Validation, I created a drop down list for people to select a name, then what I was hoping is that when they select a name from the drop down list....another column would auto populate that name's employeeID.

I don't know how to create the VLOOKUP in combination with the drop down list.

Can someone help?

 

[Smitty]

Welcome to the Board!

Here's an example using VLOOKUP & INDEX/MATCH:

Book1
	A	B	C	D
1	Name	Emp ID
2	Smith, Jane	123456
3	Smith, John	123457
4	Smith, Michael	123458
5
6	Validation
7	Smith, John	123457	INDEX/MATCH
8	Smith, John	123457	VLOOKUP
Sheet1

Hope that helps,

 

[shanealbrandt]

Wow....now I am more confused than I was before. LOL!! Sorry I may be more of a novice than I sounded at first. Is there any way to attach my spreadsheet as a file to this thread so someone can get an idea of what I am trying to do?

I think once you are able to open it and see it, it will be clearer.

Thoughts?

-S

 

[pbt]

What Smitty was trying to show you is:

You would have to have your list of employess and their ID #'s set up somewhere.

Set up your Data Validation for the cell that you want your drop down to be in. Which you already say that you can do.

Next to the cell with your drop down you enter one or the other formulas as posted by Smitty. Of course you may have to adjust the ranges to fit where your list is located.

Harry

EDIT: After relooking at your thread, Smitty has set up the list as you have posted, Col A & B

 

[shanealbrandt]

Perfect! Thank you very much, I got it to work. Now one final question....

Until you select a name from the drop down, the EmplID colum says "#NA". Is there a way around this? Is there a way to just have it appear blank, and then when you make your slection of name, it populates the EmplID field?

 

[Smitty]

You can use =IF(ISNA(VLOOKUP(),"",VLOOKUP), which can be kind of expensive because it forces 2x calculation, or better yet, you can test the validation cell itself:

=IF(ValidationCell="","",VLOOKUP())

Which says: if the validation cell equals nothing, then show nothing, otherwise perform the VLOOKUP.

 

[shanealbrandt]

I don't understand that, so....well moving back to the first problem. When I try to Copy/Paste the formula, it keeps changing the formula contents. I want it to stay the same...FOR THE MOST PART.

Let me explain.....
Formula for first cell is:
=INDEX(E2:E4276,MATCH(L2,F2:F4276,0),0)

Formula for next cell SHOULD BE:
=INDEX(E2:E4276,MATCH(L3,F2:F4276,0),0)

Instead, when I copy/paste it's changing all the variables, instead of just the "L" variable, so it looks like this:
=INDEX(E3:E4277,MATCH(L3,F3:F4277,0),0)

I need all the "E" expressions and "F" expressions to remain the same, only the "L" should be changing. Any ideas?

-S

 

[Smitty]

Take a look at Absolute References in the helpfile.

You can make the references static with $:

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

That will allow just the 2 in L2 to change, everything else will stay the same.