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