Hello All,

I have a list of two columns. In Sheet1 Column A lists the names of all
departments. Column B lists the names of personnel.

In sheet2 I have data validation in Column A where an in cell drop down lists
all the departments in column A of sheet1. In column B data validation again. I
want the in cell drop down list in column B (Shhet2) to show only the names of
those personnel whose department is selected in column A (sheet2). For example,
if "HR" is selected in column A then in column B in the same row only those who
work in HR should show. I tried putting this as a name by following formula and
use this in column B (sheet 2) data validation:

=offset(indirect(Address(match(Sheet2!A1,DeptName, 0),2,,,"Sheet2")),0,0,co
untif(deptname,Sheet2!A1),1)

where deptname is another defined name for Sheet1 Column A.

This works as long the department names is sorted in department order. If they
are in different order it doe not. I can see the problem is with countif.
However, can not figure out a solution. Help would be appreciated.

Thanks in advance.

Regards

Uddin