ExcelBanter - Find Max value

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Find Max value (https://www.excelbanter.com/excel-discussion-misc-queries/118777-find-max-value.html)

Terri

Find Max value

Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

Scott

Find Max value

Try the following, entered with CTRL+SHIFT+ENTER:

=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2!$C$2:$C$8)

Scott

Terri wrote:
Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

Scott

Find Max value

Oops... I can't read. Highest revision, not $$$.

=SUMPRODUCT(--(Sheet2!$A$2:$A$8=A2),--(Sheet2!$B$2:$B$8=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2 !$B$2:$B$8)),Sheet2!$C$2:$C$8)

Scott

Scott wrote:
Try the following, entered with CTRL+SHIFT+ENTER:

=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2!$C$2:$C$8)

Scott

Terri wrote:
Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

Terri

Find Max value

Thanks for the quick reply.
I left out a valuable piece of information, very sorry!!!
Sometimes a revision to the work order can result in a lower number, that's
why I need it to look at the revision number and give the the total for the
highest revision number of that project.

Does that make better sense?

Thanks again,

Terri

"Scott" wrote:

Try the following, entered with CTRL+SHIFT+ENTER:

=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2!$C$2:$C$8)

Scott

Terri wrote:
Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

Bob Phillips

Find Max value

=INDEX(Sheet2!$C$1:$C$20,MAX(IF(Sheet2!$A$2:$A$20= A2,ROW(Sheet2!$B$2:$B$20))
))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Terri" wrote in message
...
Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match
the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

Terri

Find Max value

Thanks,

I tried this a couple of times and it is giving me the highest revision not
the dollar amount of the highest revision.

"Bob Phillips" wrote:

=INDEX(Sheet2!$C$1:$C$20,MAX(IF(Sheet2!$A$2:$A$20= A2,ROW(Sheet2!$B$2:$B$20))
))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Terri" wrote in message
...
Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match
the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

Terri

Find Max value

Cool that works, thanks a bunch for the quick and very helpful reply.

T

"Scott" wrote:

Oops... I can't read. Highest revision, not $$$.

=SUMPRODUCT(--(Sheet2!$A$2:$A$8=A2),--(Sheet2!$B$2:$B$8=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2 !$B$2:$B$8)),Sheet2!$C$2:$C$8)

Scott

Scott wrote:
Try the following, entered with CTRL+SHIFT+ENTER:

=MAX((Sheet2!$A$2:$A$8=A2)*Sheet2!$C$2:$C$8)

Scott

Terri wrote:
Please help with formula
In the WO$ cell on worksheet 1, I need it to look on worksheet 2, match the
project number, find the highest revision and return the $$$ amount.
Worksheet 2 is not sorted in any specific way.

WS1

Project Description WO$
1 Test 1
2 Test 2
3 Test 3

WS2

Project Revision $$$
1 1 1,000
2 1 2,000
3 1 2,000
2 2 5,000
2 3 10,000
1 2 6,000
3 2 8,000

WS1 Result would be

Project Description WO$
1 Test 1 6,000
2 Test 2 10,000
3 Test 3 8,000

All times are GMT +1. The time now is 10:24 PM.