Select One

[Joe]

You guys have helped me before, so I am back.

I have 200 employees divided among 15 Supervisors.

If I create an Excel spreadsheet cell with a drop-down menu of employees,
once an employee is selected from that list, what formula can I use to have
Excel populate the employee’s Supervisor name in a cell to the right?

Example: (two cells – cell #1 selected through a drop-down menu – Cell #2 is
automatically populated when a name is chosen in cell #1)

Employee Supervisor
John Smith Bill Thompson

I have already created two separate columns, one with the employee names,
the second with the Supervisor name.

Thank you.

 

[Gord Dibben]

VLOOKUP


Gord Dibben MS Excel MVP

 

[Joe]

I'm still not there. I have tried this formula...

In cell B3:

=VLOOKUP(A3,AA100:AA309,AB100:AB309)

* Whereas A3 is the cell with the drop-down menu (created with data
validation,)
* AA100:AA309 is the column of data used to create the drop-down (the
employee names,) and
* AB100:AB309 is the column of data I want to populate (the Supervisor
names) into cell B3.

In the data source, the Supervisor name is in the column to the right of the
corresponding employee name. Much the same way I want it to populate in the
finished product.

BACKGROUND: This database is to show the number of times an employee
contacts the help center. Each person staffing the help center records the
employee name, Supervisor, question asked, and the response. I need the two
in separate Employee/Supervisor columns for sorting.

Again, thank you.

Joe

 

[John]

Hi Joe
Try it this way:
=VLOOKUP(A3,AA100:Ab309,2,FALSE)
HTH
John

 

[Joe]

Hi Joe
Try it this way:
=VLOOKUP(A3,AA100:Ab309,2,FALSE)
HTH
John

John,

That did the trick. However, if I copy/paste to all my cells, I noticed
Excel had a tendency to update the entire formula. For example – instead of…

=VLOOKUP(A3,AA100:AB309,2,FALSE)
=VLOOKUP(A4,AA100:AB309,2,FALSE)
=VLOOKUP(A5,AA100:AB309,2,FALSE)
=VLOOKUP(A6,AA100:AB309,2,FALSE) etc;

I got…

=VLOOKUP(A3,AA100:AB309,2,FALSE)
=VLOOKUP(A4,AA101:AB310,2,FALSE)
=VLOOKUP(A5,AA102:AB311,2,FALSE)
=VLOOKUP(A6,AA103:AB312,2,FALSE)

It was no big deal to correct. I just thought it was interesting Excel
updated everything.

Again, thank you for your help and expertise.

Joe

 

[Jef Gorbach]

John,

That did the trick.  However, if I copy/paste to all my cells, I noticed
Excel had a tendency to update the entire formula.  For example – instead of…

=VLOOKUP(A3,AA100:AB309,2,FALSE)
=VLOOKUP(A4,AA100:AB309,2,FALSE)
=VLOOKUP(A5,AA100:AB309,2,FALSE)
=VLOOKUP(A6,AA100:AB309,2,FALSE) etc;

I got…

=VLOOKUP(A3,AA100:AB309,2,FALSE)
=VLOOKUP(A4,AA101:AB310,2,FALSE)
=VLOOKUP(A5,AA102:AB311,2,FALSE)
=VLOOKUP(A6,AA103:AB312,2,FALSE)

It was no big deal to correct.  I just thought it was interesting Excel
updated everything.

Again, thank you for your help and expertise.

Joe

Change your first formula to =VLOOKUP(A3,$AA$100:$AB$309,2,FALSE)
then copy it down the column.
the $ tells Excel to keep those cell references asis (static) instead
of relative as per normal.

 

[John]

Hi Joe
You're welcome.
Type "Relative & Absolute' in Excel Help, this will bring up all the
information you need when you copy formulas.
Regards
John