Excel linking data

[rayh]

Hello,

I am looking for way to take data from worksheet 1 and link it to
worksheet 2. However, I only want it to show the most current date.

This is what what worksheet 1 currently looks like:

Company Date Amount
ABC 2/2/2007 100
ABC 1/13/2007 200
DEF 12/13/2006 350
DEF 1/18/2007 700
GHI 1/23/2007 900
GHI 10/16/2006 250

This is what I want worksheet 2 to look like, dynamically:

Company Date Amount
ABC 2/2/2007 100
DEF 1/18/2007 700
GHI 1/23/2007 900


Is this possible?

Kind Regards,

Ray

 

[Ragdyer]

Assuming your list on Sheet1 goes from A2 to C30.
Assuming you have a list of unique company names in Column A of Sheet2.

Format Column B of Sheet2 to dates.
Enter this formula in B2 of Sheet2:

=IF(A2<>"",SUMPRODUCT(MAX((Sheet1!$A$2:$A$30=A2)*Sheet1!$B$2:$B$30)),"")

Copy down as far as your list of customers to get a list of your latest date
for each customer.

Now, to get the amount for that date, for that customer, enter this *array*
formula in C2 of Sheet2:

=IF(B2<>"",INDEX(Sheet1!$C$2:$C$30,MATCH(1,(Sheet1!$B$2:$B$30=B2)*(Sheet1!$A$2:$A$30=A2),0)),"")

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as far as your list of customers in Column
A.