excel need to link names to courses done

I Have been able to link the Groups on the left E,G Drop down menu (name & number) to go in to box under “Employee name & works number group 1”using “VLOOKUP”.
Now I am stuck with “course name & date” I need to link the name to courses that the named has done there are about 170 men spilt in to 7 groups.
So as you select name from (on left drop down menu) the name is entered under “employee name & number” (got that) the goal is to link names to courses & show as a drop down menu.
When you change name to next name the courses that man has done changes to the courses he has done.
On attached sheet is list of a few courses names line 32 note not all names or courses entered
Any help is very much appreciated
Tried different formulas to no avail
Note office 2003

Hi,

this explanation is a tad better than in your previous post, so here comes a formula:

in I5 use

=INDEX($I$33:$K$71,MATCH(G5,$K$33:$K$71,0),1)

in I8 use

=INDEX($I$33:$M$71,MATCH(G8,$M$33:$M$71,0),1)

for each following formula in column I, you need to add two columns to the INDEX range and move the MATCH range by two columns, so in I11 it would be

=INDEX($I$33:$O$71,MATCH(G8,$O$33:$O$71,0),1)

hth

Hi Hugrl,

a drop-down list is used for data entry. You seem to want to use it for displaying data that is calculated from somewhere? What is the purpose of the cell? What do you want to achieve?

If you want to create a list of which person did which course, your data layout is not the best. You'd be much better off with a simple list like

name - group - date - course

and then you can use tools like Autofilter or Pivot Tables to summarise and see all courses for one person and much more. But with your current data layout, any kind of summary will be a pain to create.

cheers