Formula Question - Related to MAX 1

[debbiezzzzz]

Hello,

I have a user that is utilizing the MAX function to obtain the highest value of specific cells covering MANY separate worksheets in a workbook. The value is correct, but it would help him tremendously if he could also obtain the sheet name and cell that contains this max value.

Example: Workbook with 3 Sheets. (Sheet1, Sheet2, Sheet3)
The user is comparing the values of Sheet1 Cell A1, Sheet2 Cell B2, and Sheet3 Cell C1) The values in these 3 cells are 100,200, and 300 respectively.

The formula =MAX(A1,Sheet2!B2,Sheet3!C1) returns the value of 300 correctly.

Is there a way to also obtain the fact that this value was from Sheet 3 Cell C1?

Any help is appreciated.

Fam

 

[SkipVought]

Hi,

[tt]
=MAX(A1,Sheet2!B2,Sheet3!C1)

=if(MAX(A1,Sheet2!B2,Sheet3!C1)=A1,"Sheet1"
,if(MAX(A1,Sheet2!B2,Sheet3!C1)=Sheet2!B2,"Sheet2"
,if(MAX(A1,Sheet2!B2,Sheet3!C1)=Sheet3!C1,"Sheet3","")))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[debbiezzzzz]

Thanks That is perfect!

 

[randy700]

=MAX(A1,Sheet2!B2,Sheet3!C1)

=if(MAX(A1,Sheet2!B2,Sheet3!C1)=A1,"Sheet1"
,if(MAX(A1,Sheet2!B2,Sheet3!C1)=Sheet2!B2,"Sheet2","Sheet3"))


Randy