Indeed, thank you very much.

I did actaully solve it by writing a very long formula compirisng
CELL/MID/INDEX/MATCH/INDIRECT but yours is much better.

Thank you.

Colin.

"Ashish Mathur" wrote in message
...
Hi,

You may want to try my solution posted at;

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Col" wrote in message
...
Hello all,

Wonder if you kind souls can help me out with this one.
The office mangers in my territory need to complete some information for
me and I'm trying to make that operation as easy as possible. One of
which is automatically populating a particular sheet with their office
staff details from another sheet containing details of all the staff in
the company.

If the data layout is similar to below;

Office no OfficeName Forename Surname
4 Manchester Name Name
4 Manchester Name Name
4 Manchester Name Name
5 Leeds Name Name
5 Leeds Name Name

How can I bring across multiple lines and use a formula so when the
office manager at Manchester for example inputs a '4' in A1 the names of
only his staff appear underneath.
I use Index/Match regularly without problems but now I'm stumped as it's
dealing with multiple lines or info.

Many thanks for any advice,


Colin.