Dynamic range in excel validation

I have an excel sheet which contains employee information. Name, surname, department, role are columns o开发者_运维百科f this sheet.

I have another sheet in the excel file: roles. This sheet has two columns: Department and Roles.

If user enters its role as IT in the employee sheet, he/she must only choose IT related roles in the role column.

I can show all roles from role sheet by using data validation list. But how can I use custom filters for allowance of selecting roles according to the selected departments?

(My office version is 2003)