grab max date from list

[jenn]

I have several columns of data. The most important being a client name and
their last date of service. Can I pivot and pull the patient name and only
grab their last date of service, or is there a vlookup that will grab the
most recent date?

[Biff]

Hi!

Why pull and grab when you can yank! <g>

Client names in column A
Dates in column G

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A1:A100="Client_Name",G1:G100))

Format the cell as DATE

Biff

>-----Original Message-----
>I have several columns of data. The most important being
a client name and
>their last date of service. Can I pivot and pull the
patient name and only
>grab their last date of service, or is there a vlookup
that will grab the
>most recent date?
>.
>

[Ken Wright]

=SUMPRODUCT(MAX((RngNames=E1)*(RngDates)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"jenn" <jenn@discussions.microsoft.com> wrote in message
news:D280044C-6254-4AAF-9C36-2A5DA61321BA@microsoft.com...
> I have several columns of data. The most important being a client name and
> their last date of service. Can I pivot and pull the patient name and only
> grab their last date of service, or is there a vlookup that will grab the
> most recent date?

[jenn]

I must not have something quite right... when I follow the instructions my
column fills with the formula... =MAX(IF(A1:A100="Client_Name",G1:G100))

"Biff" wrote:

> Hi!
>
> Why pull and grab when you can yank! <g>
>
> Client names in column A
> Dates in column G
>
> Entered as an array with the key combo of CTRL,SHIFT,ENTER:
>
> =MAX(IF(A1:A100="Client_Name",G1:G100))
>
> Format the cell as DATE
>
> Biff
>
> >-----Original Message-----
> >I have several columns of data. The most important being
> a client name and
> >their last date of service. Can I pivot and pull the
> patient name and only
> >grab their last date of service, or is there a vlookup
> that will grab the
> >most recent date?
> >.
> >
>

[Ragdyer]

Sounds like your cells were probably formatted as Text *before* you entered
the formula.

To check if this is so, select one of the "bad" cells displaying the
formula, and then:

<Ctrl> <Shift. <~>
Then <F2>
Then <Enter>

What this does is use a keyboard shortcut to format the cell to General,
then re-enter the formula.

If this *doesn't* work, another possibility is that you're in "formula view
mode".

To toggle in and out of this view, hold <Ctrl>, and then hit the key that
has the <~> on it.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jenn" <jenn@discussions.microsoft.com> wrote in message
news:EBE185BB-024E-48A2-A3C4-931C7804AAF0@microsoft.com...
> I must not have something quite right... when I follow the instructions my
> column fills with the formula... =MAX(IF(A1:A100="Client_Name",G1:G100))
>
> "Biff" wrote:
>
> > Hi!
> >
> > Why pull and grab when you can yank! <g>
> >
> > Client names in column A
> > Dates in column G
> >
> > Entered as an array with the key combo of CTRL,SHIFT,ENTER:
> >
> > =MAX(IF(A1:A100="Client_Name",G1:G100))
> >
> > Format the cell as DATE
> >
> > Biff
> >
> > >-----Original Message-----
> > >I have several columns of data. The most important being
> > a client name and
> > >their last date of service. Can I pivot and pull the
> > patient name and only
> > >grab their last date of service, or is there a vlookup
> > that will grab the
> > >most recent date?
> > >.
> > >
> >