I have a list of all players names in column B and then allocate each a
division number in column C ( next to each name ).
Something like this on sheet1?
A B C
ColA names divno
n1 1
n2 2
n3 3
n4 4
n5 1
n6 2
n7 3
n8 4

I need a formula to copy
the correct name into their allocated division on another sheet automatically
when a number is entered in column C. ie each Division will have its own
sheet so instead of retyping all the names i need a formula to create each
division by just entering the number next to each players name.


On sheet 2 for division 1 you want to have all names from sheet 1 belonging
to division1 like this?

ColA names divno
n1 1
n5 1
There is no such formula, you can do that with a Change event macro for
sheets 2,3,4,5:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C2" Then
Application.EnableEvents = False
Worksheets("Munka1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=Target.Value
Selection.CurrentRegion.Copy
Destination:=Worksheets("Munka2").Range("A1")
Selection.AutoFilter
Worksheets("Munka2").Select
Application.EnableEvents = True
End If
End Sub

It will create the filtered list for each division if you enter division No
in C2.
You must have a Column header in A1, too!
You have to install the event sub in all division sheets!

Hope this meets your requirements.

Regards,
Stefi