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

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,

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

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

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