conditional lists

[Jean-Francois Gauthier]

Hi there,

I have a workbook, with a bunch of sheets in it for references.

On the first sheet, called EmployeeInformationForm, I have cell D7 that is a
validation list from the Departments sheet. When I select from the drop down
list a department name, I have the department code that is automatically
placed in cell J7. Cell J7 is =VLOOKUP(D7,Departments!A2:B4,2,FALSE).

What I would like to do next is have the following done. Cell D29, which is
the employee status, looks up the status sheet for different codes. These
codes all have different departments attached to them, kind fo like below.

Department StatusCode StatusText

So what I want the cell to do is look up the value in cell J7 and match it
to ANY of the cells B1:B999999 in sheet Status, and for every match between
J7 and the cell reference, return a list of the according status text (third
column).

So if D7 is FIN, then J7 become 60.

in D29, the drop down would show all of the statustext in sheet status, that
have the departmetn code equal that of what is in cell J7.

Is this possible?

Thanks,

JF