Select One - Populate Another?

[josephezell]

I need help finding what formula I need to create this in Access 2007.

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

I have already created the drop-down menu. Now, I need the formula for making the Supervisor name populate in the column to the right of the selection.

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 created two columns, one with 209 employee names, the second with the Supervisor name next to the corresponding employee name.

Thank you.

Joe

 

[Joe4]

This appears that it might be an Access question (based on your first sentence), but you mention cells and columns (Excel terms) and have posted this in the Excel forum.

So is it an Access or Excel question?

 

[josephezell]

I'm an idiot - it's Excel. I have no idea why I typed Access.

 

[twistinginthewind]

Sounds like you need a simple vlookup...

=vlookup(selected employee name, table of employee and supervisors,2,false)

Sorry for psuedo code, but didn't see atcualy ranges listed in the post.

 

[josephezell]

Psuedo code is fine. And, thank you for the reply. I am now using your suggestion.

For my case, one of the cells shows...

=VLOOKUP(A10,AA108:AB317,2,FALSE)

A10 is the drop down menu selection (changes with each different cell),
AA108:AB317 is the two column list of names.

Funny thing, it doesn't work 100% of the time. I have the code set at FALSE. I checked to make sure the employee/supervisor names are correct. But, on one particular name, every time I select it, it does NOT populate the supervisor name. It does not populate ANY name - just blank. Weird!

Is there a method of assuring it populates?

 

[josephezell]

Psuedo code is fine. And, thank you for the reply. I am now using your suggestion.

For my case, one of the cells shows...

=VLOOKUP(A10,AA108:AB317,2,FALSE)

A10 is the drop down menu selection (changes with each different cell),
AA108:AB317 is the two column list of names.

Funny thing, it doesn't work 100% of the time. I have the code set at FALSE. I checked to make sure the employee/supervisor names are correct. But, on one particular name, every time I select it, it does NOT populate the supervisor name. It does not populate ANY name - just blank. Weird!

Is there a method of assuring it populates?

UPDATE: I think I see the problem! The AA and BB formula's are changing too, and they should remain the same. I believe I need to manually enter each formula. It's only 50 cells.

The (psuedo) code should be...

=VLOOKUP(left hand cell,column AA100 thru AA309:AB100 thru AB309,2,FALSE)

...and that's not what I have.

 

[josephezell]

Yep. That was the problem. Here is the final code...

[INDENT]=VLOOKUP(A3,AA101:AB310,2,FALSE)
=VLOOKUP(A4,AA101:AB310,2,FALSE)
=VLOOKUP(A5,AA101:AB310,2,FALSE)
=VLOOKUP(A6,AA101:AB310,2,FALSE)
=VLOOKUP(A7,AA101:AB310,2,FALSE)
=VLOOKUP(A8,AA101:AB310,2,FALSE)
=VLOOKUP(A9,AA101:AB310,2,FALSE)
=VLOOKUP(A10,AA101:AB310,2,FALSE)
=VLOOKUP(A11,AA101:AB310,2,FALSE)
=VLOOKUP(A12,AA101:AB310,2,FALSE)
=VLOOKUP(A13,AA101:AB310,2,FALSE)
=VLOOKUP(A14,AA101:AB310,2,FALSE)
=VLOOKUP(A15,AA101:AB310,2,FALSE)
=VLOOKUP(A16,AA101:AB310,2,FALSE)
=VLOOKUP(A17,AA101:AB310,2,FALSE)
=VLOOKUP(A18,AA101:AB310,2,FALSE)
=VLOOKUP(A19,AA101:AB310,2,FALSE)
=VLOOKUP(A20,AA101:AB310,2,FALSE)
=VLOOKUP(A21,AA101:AB310,2,FALSE)
=VLOOKUP(A22,AA101:AB310,2,FALSE)
=VLOOKUP(A23,AA101:AB310,2,FALSE)
=VLOOKUP(A24,AA101:AB310,2,FALSE)
=VLOOKUP(A25,AA101:AB310,2,FALSE)
=VLOOKUP(A26,AA101:AB310,2,FALSE)
=VLOOKUP(A27,AA101:AB310,2,FALSE)
=VLOOKUP(A28,AA101:AB310,2,FALSE)
=VLOOKUP(A29,AA101:AB310,2,FALSE)
=VLOOKUP(A30,AA101:AB310,2,FALSE)
=VLOOKUP(A31,AA101:AB310,2,FALSE)
=VLOOKUP(A32,AA101:AB310,2,FALSE)
=VLOOKUP(A33,AA101:AB310,2,FALSE)
=VLOOKUP(A34,AA101:AB310,2,FALSE)
=VLOOKUP(A35,AA101:AB310,2,FALSE)
=VLOOKUP(A36,AA101:AB310,2,FALSE)
=VLOOKUP(A37,AA101:AB310,2,FALSE)
=VLOOKUP(A38,AA101:AB310,2,FALSE)
=VLOOKUP(A39,AA101:AB310,2,FALSE)
=VLOOKUP(A40,AA101:AB310,2,FALSE)
=VLOOKUP(A41,AA101:AB310,2,FALSE)
=VLOOKUP(A42,AA101:AB310,2,FALSE)
=VLOOKUP(A43,AA101:AB310,2,FALSE)
=VLOOKUP(A44,AA101:AB310,2,FALSE)
=VLOOKUP(A45,AA101:AB310,2,FALSE)
=VLOOKUP(A46,AA101:AB310,2,FALSE)
=VLOOKUP(A47,AA101:AB310,2,FALSE)
=VLOOKUP(A48,AA101:AB310,2,FALSE)
=VLOOKUP(A49,AA101:AB310,2,FALSE)
=VLOOKUP(A50,AA101:AB310,2,FALSE)
=VLOOKUP(A51,AA101:AB310,2,FALSE)[/INDENT]

Hope that helps someone in the future.