Max Date

S

Stacy

I have 2 columns, one is a version one is the date:
Based on the version, I want to find the max date.

Version Date
- - - - - - -
MA 10/01/03
IH 10/07/03
FR 09/05/03
MA 10/08/03
FR 10/07/03

So I want to be able to get the Max Date for FR - 10/07/03 and put it
in cell A1 and get the max Date for MA - 10/08/03 and put it in cell
A2.

Any suggestions?
Thanks,
Stacy
 
J

J.E. McGimpsey

One way:

Dim rng1 As Range
Set rng1 = Range("J1:K7")
Range("A1").Value = Evaluate("MAX((" & rng1.Columns(1).Address _
& "=""FR"")*(" & rng1.Columns(2).Address & "))")
Range("A2").Value = Evaluate("MAX((" & rng1.Columns(1).Address _
& "=""MA"")*(" & rng1.Columns(2).Address & "))")

Adjust rng1 to suit your table.
 
S

Stacy

Works PERFECTLY!
Thanks so much!

Stacy

J.E. McGimpsey said:
One way:

Dim rng1 As Range
Set rng1 = Range("J1:K7")
Range("A1").Value = Evaluate("MAX((" & rng1.Columns(1).Address _
& "=""FR"")*(" & rng1.Columns(2).Address & "))")
Range("A2").Value = Evaluate("MAX((" & rng1.Columns(1).Address _
& "=""MA"")*(" & rng1.Columns(2).Address & "))")

Adjust rng1 to suit your table.
Click to expand...
 
S

Stacy

I have two more questions:

I have multiple worksheets in my workbook, all of which I want the max
date of Sheet1. Is there a way to evaluate the date on Sheet2 using
the range from Sheet1 without copying and pasting?

I also have a second file that I would like to put these dates in
based on the first file, is there a way to use the range from one file
and put the output in another?

Am I just better off copying and pasting once the original calculation
is made?

Thanks for any suggestions.
Stacy
 
J

J.E. McGimpsey

In your second sheet, select the cell you want to have the first max
date in, and type "=" (without the quotes). While in edit mode,
switch to the first sheet and click on the max date. Type Enter. XL
will enter the link. Or you can directly type:

='Sheet1'!A1

Note that the single quotes are only really necessary if the sheet
name has a space in it.

You can do the same thing with the other workbook. You'll end up
with something like:

='[Book1.xls]Sheet1'!A1

while your first (source) workbook is open. When it's closed XL will
display the file path as well.
 
S

Stacy

Thank you so much for your help.
It works great!


J.E. McGimpsey said:
In your second sheet, select the cell you want to have the first max
date in, and type "=" (without the quotes). While in edit mode,
switch to the first sheet and click on the max date. Type Enter. XL
will enter the link. Or you can directly type:

='Sheet1'!A1

Note that the single quotes are only really necessary if the sheet
name has a space in it.

You can do the same thing with the other workbook. You'll end up
with something like:

='[Book1.xls]Sheet1'!A1

while your first (source) workbook is open. When it's closed XL will
display the file path as well.


I have two more questions:

I have multiple worksheets in my workbook, all of which I want the max
date of Sheet1. Is there a way to evaluate the date on Sheet2 using
the range from Sheet1 without copying and pasting?

I also have a second file that I would like to put these dates in
based on the first file, is there a way to use the range from one file
and put the output in another?

Am I just better off copying and pasting once the original calculation
is made?
Click to expand...
Click to expand...
 
Top