One play which automates it using non-array formulas ..
(I'm using xl03, but think it should work just as well in your xl07)

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,MA TCH(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
---
"Greggers" wrote:
Dear All,
Im trying to display data on separate worksheets depending on reference
numbers on a source worksheet SheetA. The number of rows of data for each
reference number VARIES, so I cannot just reference the position on worksheet
SheetA. VLOOKUP fails because it needs a unique reference number for each
row.

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