Max Date

  • Thread starter Thread starter Stacy
  • Start date Start 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
 
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.
 
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...
 
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
 
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.
 
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...
 
Back
Top