MAX value found based on date range

[joecnelson77]

So I have a sheet (sheet1) with data setup as such:



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]01/01
[/TD]
[TD]01/02
[/TD]
[TD]01/03
[/TD]
[TD]01/04
[/TD]
[TD]01/05
[/TD]
[TD]01/06
[/TD]
[TD]01/07
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]300
[/TD]
[TD]400
[/TD]
[TD]600
[/TD]
[TD]450
[/TD]
[TD]50
[/TD]
[/TR]
</tbody>[/TABLE]


On sheet2, I would like to use a formula that looks at this data range and and show me that from 01/01 - 01/07, 600 was the MAX value that was found and return that 600 value.

For arguments sake, sheet1 starts at A1.

Any suggestions?

 

[Gavin T]

The max function will do that.

MsgBox Application.Max(Range("A2:G2"))

 

[Fizzz]

I have a question, why are you referencing the date range? Your formula is going to look ONLY at the values, so by just using =MAX(Sheet1!A2:G2), you would get what you want.

Is there any other condition that would change the date range?