Lookup Rows in a sheet

  • Thread starter Thread starter Rudi Groenewald
  • Start date Start date
R

Rudi Groenewald

Hi people,

I've got a range of data on one sheet that changes often. these one of the
coloumns is called "Originator" which has the name of a person in it.

What I'd like to do is get excel to place each person's rows into his own
sheet. This will always be about 7 people, so I can create the sheet
templates myself , but how do I create a lookup formula to retrieve all the
records where for eg the name "john" is in the originator coloumn. Return
(copy) those records (rows) from the "DATA" sheet to the sheet "JOHN"


Please help.

thanks alot in advance guys, what you are doing is great
Rudi Groenewald
 
Maybe one way ..

In Sheet1
-------------
Assume the "master" table is in cols A to D
data from row2 down
(with the key column "Originator" in col D), viz:

Job Student Supervisor Originator
Job1 Student1 Supervisor1 John
Job2 Student2 Supervisor2 Ben
Job3 Student3 Supervisor3 John
etc

List across in F1:G1,
the Originators: John, Ben

Put in F2: =IF($D2="","",IF($D2=F$1,ROW(),""))

Copy F1 across to G2, then fill down by a safe "max"
expected number of rows of data in cols A to D,
say down to G1000

In a new Sheet2
-----------------------
Let's reserve cell A1 for the originator's name
Input in A1: John

Put the same col headers into A2:D2, viz.:
Job Student Supervisor Originator

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+
64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRECT("Sheet1!$"&C
HAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64))
,0)),"",OFFSET(Sheet1!$A$1,MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,S
heet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRE
CT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet
1!$1:$1,0)+64)),0)-1,COLUMN(A1)-1))

(Restore the inadvertent line wraps / line breaks when you copy > paste the
above formula)

Copy A3 across to D3, then fill down by as many rows
as was done in Sheet1's cols F and G, say down to D1000

You'll see that cols A to D (in row3 down)
will auto-return the "filtered" rows from Sheet1
for the Originator: John input in A1,
i.e. for the sample data-set above, it'll appear as:

Job Student Supervisor Originator
Job1 Student1 Supervisor1 John
Job3 Student3 Supervisor3 John

Now just duplicate/make a copy of Sheet2,
change the input in A1 in the new sheet to: Ben
and you'll get the "filtered" rows for Ben:

Job Student Supervisor Originator
Job2 Student2 Supervisor2 Ben

And if you re-label the default sheetnames
with the Originator names: John, Ben

you could also then put in cell A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

which will auto-extract the sheetname into cell A1
(instead of having to input into cell A1)

Adapt / extend to suit ..
 
Back
Top