Excel linking data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rayh@patriots.com - 04 Jun 2007 18:07 GMT

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

Reply to this Message

Ragdyer - 04 Jun 2007 23:45 GMT

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)),"")

Signature

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Hello,
>
[quoted text clipped - 23 lines]
>
> Ray

Reply to this Message

Rate this thread: