It now works fine. Thank you very much.
--
George


"Max" wrote:

One non-array formulas play which could deliver the required results ..

Assuming source data below is in Sheet2, cols A to C, from row2 down

Surname FirstName Date
Tyson Michael 04/05/99
Nelson Horatio 06/11/94
Jones Michael 12/08/65
Tyson Butch 07/31/84
etc

In Sheet1,
the surname of interest will be input in M4
(Labels placed in O3:Q3 : Surname, FirstName, Date)

Put in N4:
=IF($M$4="","",IF($M$4=Sheet2!A2,ROW()-2,""))
(Leave N1:N3 blank)

Put in O4:
=IF(ROW(A1)COUNT($N:$N),"",INDEX(Sheet2!A:A,SMALL ($N:$N,ROW(A1))))
Copy O4 to Q4. Select N4:Q4, fill down to cover the max expected extent of
source data in Sheet2, say down to Q200. Hide away col N. Format col Q as
date. Cols O to Q will return the required results for the surname input in
M4.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"George" wrote:
It`s not working. I keep getting a message "Reference is not valid". Is it
possible to just have two sheets, rather then three?
--
George


"Allllen" wrote:

use data filter advanced filter. It is easier than a macro and pretty
powerful.

sheet2 (data)
Surname FirstName Date
Tyson Michael 04/05/99
Nelson Horatio 06/11/94
Jones Michael 12/08/65
Tyson Butch 07/31/84

sheet3 (criteria)
Surname FirstName Date
Tyson

sheet4 (where you will put the results)
Surname FirstName Date

now do this
- data filter advanced filter
- copy to another location
- list range (select your data on sheet2)
- criteria range (select sheet3 A1:C2)
- copy to (select sheet4 A1:C1)

ok

--
Allllen


"George" wrote:

I have a table (sheet2), which in column `A` have surnames and column `B`
first names, column `C` a date. What I`m after is to enter a surname in cell
M4(sheet1), this produces a list of all clients with that surname and first
names and date. I was after a vb code for this procedure. Can anybody help.
--
George