I've spent hours trawling through the various answers to this but
can't get one that will help in this specific case.

I have a a list of names in one column. Each name corresponds to a
department number in the adjacent column.

Lets say there are 10 names and three dept numbers.

I would like to aggregate the names that correspond to each department
in a simple columnar list on a separate sheet. However, I would also
like to be able to add names at the bottom of the list that
automatically appear on the correct department sheet when added.

I can give more detail if necessary but trying to keep it simple.

Many thanks for any help

Hugh:

We were doing just fine until I read your sentence containing "...able
to add names at the bottom...". This means that your departmental worksheets
have to have ample VLOOKUPs to cover any new addition to the "base"
worksheet. This approach means that you have to set some expected maximum
number of names that would ever be expected on the starting worksheet. Even
if you decided to use PivotTables on the departmental worksheets, you'd have
the same issue as the PivotTable ranges (back to the main worksheeet) would
have to include enough blank rows to cover growth.
Writing code in VBA makes this problem simple but your posting doesn't
suggest that you want to (or can) go this route.

Steve in Ohio

"HughWB" wrote:

I've spent hours trawling through the various answers to this but
can't get one that will help in this specific case.

I have a a list of names in one column. Each name corresponds to a
department number in the adjacent column.

Lets say there are 10 names and three dept numbers.

I would like to aggregate the names that correspond to each department
in a simple columnar list on a separate sheet. However, I would also
like to be able to add names at the bottom of the list that
automatically appear on the correct department sheet when added.

I can give more detail if necessary but trying to keep it simple.

Many thanks for any help