help with a formula

[Ben]

I have two work sheets. The first contains a list of clients while the second
records data on each occation we have a contact with our client. I want to
extract data from the second sheet that reflects the most recent entry. It
look like this:

Sheet 1
Client Ref First
Number Number Name Last Name
1 654 Sam Fisher
2 123 Sam Jones
3 688 Harry Smith
4 478 John Johanson

Sheet 2
Date
Voucher Client Centre First Last
Issued Number Ref Name Name
15/08/08 1 654 Sam Fisher
15/08/09 1 654 Sam Fisher
15/08/09 3 688 Harry Smith
15/08/09 4 478 John Johanson
15/08/09 5 987 Laurey Dessmond

As you can see client #1 has an entry on 15/8/08 and 15/08/09.
I need a formula that can return the latest date into a cell in sheet 1.

Is this possible? Please help.

Thanks

 

[Max]

One way
Assuming client num is unique, and dates in Sheet2 are real dates
In Sheet1,
In E2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula:
=MAX(IF(Sheet2!B$2:B$6=A2,Sheet2!A$2:A$6))
Copy down.

Real dates are numbers, increasing chronologically. Hence the latest date
for any particular client num would simply be its largest associated "date"
number
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Pete_UK]

Put this array* formula in E3 of Sheet1:

=MAX(IF((C3=Sheet2!D$3$300)*(D3=E$3:E$300),Sheet2!A$3:A$300))

I've assumed you have data in rows 3 to 300 - change as required to
suit your data. Format the cell as a date, then copy down as far as
needed.

* An array formula must be committed using the key combination of Ctrl-
Shift-Enter (CSE) instead of the usual <enter>. If you do ths
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
need to amend the formula you have to use CSE again.

Hope this helps.

Pete