Hi Robin
here is one way, using VBA. I will assume:
your table with all info on is called Sheet1.
You have headings in sheet1 row 1.
Your Department Column is A.
You Department list will be in cell F1 of sheet2.
1. Name your list of Departments range e.g "Deps"
2. select sheet2 cell F2 and go to Data>Validation and using the "List" option, place in the range for your departments, e.g $A$2:$A$100
3. In any other cell on sheet2 put: =Deps
4. Right click on the sheet2 name tab and select "View Code". Paste in the code below over the top of what you see.
Private Sub Worksheet_Calculate()
On Error Resume Next
If ActiveCell.Address <> "$F$2" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Sheets("Sheet1").AutoFilterMode = False Then
Sheets("Sheet1").Rows(1).AutoFilter
End If
Sheets("Sheet1").Cells(1, 1).AutoFilter Field:=1, Criteria1:=ActiveCell
Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add().Name = ActiveCell
ActiveSheet.Paste
Sheets("Sheet1").AutoFilterMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub
Change any ranges and sheet names to suit. Push Alt+Q and save.
Now each time you select a different department from your list a new sheet will be created called the department chosen. And all data pertaining to this department will be on the sheet.
Hope this helps
Dave
OzGrid Business Applications