Allocate values from a central worksheet to defined worksheets

[thomasbeard]

In Sheet1 I have a list of names next to a list of worksheet names to which they need to be allocated to. What I want to know is how I allocate these names to the correct sheet.
For example if Sheet1 tells me:


Tom Sheet2
**** Sheet3
Harry Sheet2
Pete Sheet 3

When I go to Sheet2, I would like to see:

Tom
Harry

and Sheet3 will show:

****
Pete

Any ideas?
Thanks

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Sheets(Range("B" & i).Value).Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Range("A" & i).Value
Next i
End Sub

 

[thomasbeard]

Thanks, but any chance of doing this with formula - I'm making progress with a mix of offset, match, countif and index but it's slow progress