Copy/filter data in a sheet from another sheet

[Guest]

Hi All,

I have a sheet (Sheet#1) formatted something like this:

Saadi
ID Document Name Date Status
1 xyz 12-21-2007 Open
2 xyz 12-21-2007 Closed
3 xyz 12-21-2007 Open

Sam
ID Document Name Date Status
1 xyz 12-21-2007 Open
2 xyz 12-21-2007 Closed
3 xyz 12-21-2007 Open

Norman
ID Document Name Date Status
1 xyz 12-21-2007 Open
2 xyz 12-21-2007 Closed
3 xyz 12-21-2007 Open


Now I want that when on sheet#2, I input any name from this Sheet#1 (e.g.
Saadi, Sam or Norman) in a specific cell, the information given in the cells
next to that name should be displayed.

Like When I enter Sam in that specific cell on Sheet#2 only the
information/data under same name should be populated. Can anybody help me in
this regard? Number of Rows and column are constant for all the Records (5
Rows, 5 Columns)

The name is written in a merged cell (5 columns) above the data cells of
that particular record.

 

[Guest]

Here's a link to a sample to illustrate:
http://www.freefilehosting.net/download/Mzg4MjU=
Display range from other sht.xls

Source data is assumed in Sheet1,
cols A to D, with "header" names in col A

In Sheet2,
DV to select name in A1

Put in A2:
=OFFSET(Sheet1!$A$1,MATCH($A$1,Sheet1!$A:$A,0)+ROWS($1:1)-1,COLUMNS($A:A)-1)
Copy across to D2, fill down to D5 to populate. A25 will display the
associated range for the name selected in A1.