Most Current Date

[Mark Jackson]

Hello,

I have a worksheet that I keep track of quotes submitted. I have columns:
Customer, Submit Date, Award Date, Price Quoted, and Won/Lost. I have
another worksheet that is linked that keeps a cumulative total of the quotes
that are lost. On that worksheet I have Last Award Date, Company, # of
Quotes, and Total Value. My question, is it possible to have the most
current award date for each company? I have it setup so companies that win
multiple times are listed on one row with total # awards won, total value of
awards. I would like to automatically insert the most recent award date each
time an award is issued to that company. Any help is appreciated.

Thanks.
Mark

[Barb Reinhardt]

I've done something like this recently, so here goes.

Let's say your customer names are in A2:A8.
Let's also say that the award dates are in C2:C8.

This is the equation that you'd need
=MAX(IF(A$2:A$8="Customer A",IF($C$2:$C$8<>"",$C$2:$C$8)))

You can replace "Customer A" with a variable name if you want it to be more
dynamic. You MUST enter this with CTRL-SHIFT-ENTER and you'll see {}
around it after that's been done.

Read this for more info
http://www.cpearson.com/excel/array.htm

"Mark Jackson" wrote:

> Hello,
>
> I have a worksheet that I keep track of quotes submitted. I have columns:
> Customer, Submit Date, Award Date, Price Quoted, and Won/Lost. I have
> another worksheet that is linked that keeps a cumulative total of the quotes
> that are lost. On that worksheet I have Last Award Date, Company, # of
> Quotes, and Total Value. My question, is it possible to have the most
> current award date for each company? I have it setup so companies that win
> multiple times are listed on one row with total # awards won, total value of
> awards. I would like to automatically insert the most recent award date each
> time an award is issued to that company. Any help is appreciated.
>
> Thanks.
> Mark

[Mark Jackson]

Barb,

Thank you for the quick response. It worked very well. How can I set it up
with a veriable name? Thanks again for you help.

Mark


"Barb Reinhardt" wrote:

> I've done something like this recently, so here goes.
>
> Let's say your customer names are in A2:A8.
> Let's also say that the award dates are in C2:C8.
>
> This is the equation that you'd need
> =MAX(IF(A$2:A$8="Customer A",IF($C$2:$C$8<>"",$C$2:$C$8)))
>
> You can replace "Customer A" with a variable name if you want it to be more
> dynamic. You MUST enter this with CTRL-SHIFT-ENTER and you'll see {}
> around it after that's been done.
>
> Read this for more info
> http://www.cpearson.com/excel/array.htm
>
> "Mark Jackson" wrote:
>
> > Hello,
> >
> > I have a worksheet that I keep track of quotes submitted. I have columns:
> > Customer, Submit Date, Award Date, Price Quoted, and Won/Lost. I have
> > another worksheet that is linked that keeps a cumulative total of the quotes
> > that are lost. On that worksheet I have Last Award Date, Company, # of
> > Quotes, and Total Value. My question, is it possible to have the most
> > current award date for each company? I have it setup so companies that win
> > multiple times are listed on one row with total # awards won, total value of
> > awards. I would like to automatically insert the most recent award date each
> > time an award is issued to that company. Any help is appreciated.
> >
> > Thanks.
> > Mark

[Barb Reinhardt]

Let's say your company names are variables and the first is located in A20.

=MAX(IF(A$2:A$8=A20,IF($C$2:$C$8<>"",$C$2:$C$8)))

It will still need CTRL-SHIFT-ENTER to enter.

You can then copy down without an issue.



"Mark Jackson" wrote:

> Barb,
>
> Thank you for the quick response. It worked very well. How can I set it up
> with a veriable name? Thanks again for you help.
>
> Mark
>
>
> "Barb Reinhardt" wrote:
>
> > I've done something like this recently, so here goes.
> >
> > Let's say your customer names are in A2:A8.
> > Let's also say that the award dates are in C2:C8.
> >
> > This is the equation that you'd need
> > =MAX(IF(A$2:A$8="Customer A",IF($C$2:$C$8<>"",$C$2:$C$8)))
> >
> > You can replace "Customer A" with a variable name if you want it to be more
> > dynamic. You MUST enter this with CTRL-SHIFT-ENTER and you'll see {}
> > around it after that's been done.
> >
> > Read this for more info
> > http://www.cpearson.com/excel/array.htm
> >
> > "Mark Jackson" wrote:
> >
> > > Hello,
> > >
> > > I have a worksheet that I keep track of quotes submitted. I have columns:
> > > Customer, Submit Date, Award Date, Price Quoted, and Won/Lost. I have
> > > another worksheet that is linked that keeps a cumulative total of the quotes
> > > that are lost. On that worksheet I have Last Award Date, Company, # of
> > > Quotes, and Total Value. My question, is it possible to have the most
> > > current award date for each company? I have it setup so companies that win
> > > multiple times are listed on one row with total # awards won, total value of
> > > awards. I would like to automatically insert the most recent award date each
> > > time an award is issued to that company. Any help is appreciated.
> > >
> > > Thanks.
> > > Mark

[Mark Jackson]

Barb,

Thank you! It works great. Have a great day.

Mark

"Barb Reinhardt" wrote:

> Let's say your company names are variables and the first is located in A20.
>
> =MAX(IF(A$2:A$8=A20,IF($C$2:$C$8<>"",$C$2:$C$8)))
>
> It will still need CTRL-SHIFT-ENTER to enter.
>
> You can then copy down without an issue.
>
>
>
> "Mark Jackson" wrote:
>
> > Barb,
> >
> > Thank you for the quick response. It worked very well. How can I set it up
> > with a veriable name? Thanks again for you help.
> >
> > Mark
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > I've done something like this recently, so here goes.
> > >
> > > Let's say your customer names are in A2:A8.
> > > Let's also say that the award dates are in C2:C8.
> > >
> > > This is the equation that you'd need
> > > =MAX(IF(A$2:A$8="Customer A",IF($C$2:$C$8<>"",$C$2:$C$8)))
> > >
> > > You can replace "Customer A" with a variable name if you want it to be more
> > > dynamic. You MUST enter this with CTRL-SHIFT-ENTER and you'll see {}
> > > around it after that's been done.
> > >
> > > Read this for more info
> > > http://www.cpearson.com/excel/array.htm
> > >
> > > "Mark Jackson" wrote:
> > >
> > > > Hello,
> > > >
> > > > I have a worksheet that I keep track of quotes submitted. I have columns:
> > > > Customer, Submit Date, Award Date, Price Quoted, and Won/Lost. I have
> > > > another worksheet that is linked that keeps a cumulative total of the quotes
> > > > that are lost. On that worksheet I have Last Award Date, Company, # of
> > > > Quotes, and Total Value. My question, is it possible to have the most
> > > > current award date for each company? I have it setup so companies that win
> > > > multiple times are listed on one row with total # awards won, total value of
> > > > awards. I would like to automatically insert the most recent award date each
> > > > time an award is issued to that company. Any help is appreciated.
> > > >
> > > > Thanks.
> > > > Mark