Findout Max value

[tqm1]

Dear Experts

1) In my Worksheet ("Sheet1") I have following two columns as

Code-----Date---
--7----01/02/07
--4----01/02/07
--1----01/02/07
--5----01/02/07
--1----02/08/05
--5----02/08/06

2) Sheets("sheet2").range("D4") has following date
01/02/07

How to findout Maximum Code in column A against Date=01/02/07
The answer is =7

Note: Date format is ("dd-mm-yy") 'British date

[Simon Lloyd]

Cross posted here (http://www.excelforum.com/showthread.php?t=600781)

[tqm1]

Dear Sir,

What is danger in cross posting?

By the way, read question again.
It is different than what you think coross posting.

A user try to finout soloution by different forums.

Thanks

[Simon Lloyd]

tqm1 the question you posted here is shorter than the others you have posted but smount to the same none the less!. Cross posting whilst beneficial to you means that many people give up their valuable free time to help you with a solution only to find out that someone elsewhere has been working on it and provided a solution and so find their time was wasted when they could have been helping someone else, its not the fact that you cross posted but you didnt tell anyone so they could look to see if the sloution they were heading towards has been accomplished. In future if you cross post provide the link to the other post and you are more likely to get a speedy solution!

[Bob Phillips]

=MAX(IF(Sheet1!B2:B7=D4),Sheet1!A2:A7)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[Simon Lloyd]

Bob from the other posting!

Hi,

Use an array formula like so,

=MAX(IF(date_range=date,data_range,"")) [confirm with CTRL, SHIFT & ENTER]

It may be wise to use a cell reference to reference the date to ensure you're checking for the right this, viz

eg

=MAX(IF(B1:B8=D1,A1:A8,""))

[tqm1]

Dear Sir,

I respect your orders.

This formula work fine within a cell
=MAX(IF(Sheet1!B2:B7=D4),Sheet1!A2:A7)

But if you observe my attachment,
I want to know: how to use this formula under a command button.

Please help again

[Bob Phillips]

MsgBox Evaluate("MAX(IF(Sheet1!B1:B8=Sheet2!D4,Sheet1!A1:A8))")