Lookup table with duplicate 'lookup_values'

SheetA
Ref No. Site Locn etc
01 Site M Locn A ..
01 Site M Locn B ..
01 Site M Locn C ..
01 Site M Locn D ..
03 Site N Locn F ..
03 Site N Locn H ..
03 Site N Locn T ..
03 Site N Locn U ..
03 Site N Locn K ..
06 Site R Locn S ..
06 Site R Locn V ..
06 Site R Locn W ..
.. .. ..

On SheetM, I wish to display all data associated with Ref 01.
On SheetN, I wish to display all data associated with Ref 03 etc.
Can anyone help?

I'm using Excel 2007.
Many thanks in anticipation

Illustrated in this sample:
http://www.savefile.com/files/1600987
AutoCopy Lines by Key Col to Resp Sheets.xls

In sheet: WS1 (the "master")
Assume data in cols A to C, data in row2 down,
with the key col = col A (Ref no)
(Ref nos in col A are assumed text numbers: 01, 03, etc)

List the ref nos in K1 across, in text:
01,03,06, etc (can be in any order)

Put in K2: =IF($A2=K$1,ROW(),"")
Copy K2 across as far as required,
then fill down to cover the max expected extent of source data

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named: 01
With the same col headers pasted into A1:C1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),0)))

Copy A2 across to C2, fill down to say, C10 (copy down by the smallest
possible range sufficient to cover the max expected extent for any Ref no.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)

Cols A to C will return only the lines for the Ref No: 01 from "WS1", with
all lines neatly bunched at the top

Now, just make a copy of the sheet: 01, rename it as the next Ref No: 03,
and you'd get all the results for the 03. Repeat the copy > rename sheet
process to get the rest of the Ref No sheets (a one-time job). Adapt to suit
..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks

Greggers

In my xl03, it refers to the menu command "Insert>Name",
to define named ranges
(not insert worksheet)

When I "Click Insert > Name > Define",
it opens a "Define Name" dialog
where the subsequent steps as mentioned are then done

Hope that clarifies it sufficiently for you
(I don't have/know xl07, hope the steps aren't too different)

How can I find the post giving this technique from Harlan?

Greg

As for Harlan, think I've provided you with the gist of it. You could try
google search/browse his posts in Excel newsgroups.

Greggers