Do you mean that you want defined names for Department AAA, and BBB, etc.?

If so, I think that you will need to create new independent lists of each
with the associated employees, name these and use them in the Data
Validation.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ixtreme" wrote in message
ups.com...
I have a sheet with employees and department on it. What I need is a
pice of vba code that does the following:

Update defined name ranges automatically upon adding new employees.

The sheet looks like this

A B C
1 Employee 1 Department AAA
2 Employee 2 Department BBB
3 Employee 3 Department AAA
4 Employee 4 Department CCC
5 Employee 5 Department AAA
6 Employee 6 Department DDD

The code should automatically update the values of the defined named
ranges (created via Insert, Name, Define) based on Department if a new
employee is added to this list in cell A7.

On another sheet I have a cell with a list box (via Data, Validation,
allow LIST, where source refers to the (updated) Defined range.

Mark