Most Current Date

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Barb,

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

Mark