How to identify source or location of MAX_VALUE in range

I am using Office Professional 2003. I want to extract the MAX_VALUE in a
range across several work sheets. Once the MAX_VALUE is determined in the
range, I need to identify the sheet that value resides on.

Assuming that the range of interest for your worksheets is A1:A100, and
that B1:B3 contains your sheet names, try the following...

Maximum value:

=MAX(SUBTOTAL(4,INDIRECT("'"&B1:B3&"'!A1:A100")))

Sheet name:

=INDEX(B1:B3,MATCH(MAX(SUBTOTAL(4,INDIRECT("'"&B1:B3&"'!A1:A100"))),SUBTO
TAL(4,INDIRECT("'"&B1:B3&"'!A1:A100")),0))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

In article <[email protected]>,
"Phil" <[email protected]> wrote:

> I am using Office Professional 2003. I want to extract the MAX_VALUE in a
> range across several work sheets. Once the MAX_VALUE is determined in the
> range, I need to identify the sheet that value resides on.

Domenic,

This can better be taken as a Top N problem...

Let A4:A7 house the relevant sheet names, with the label Sheets in A4:

Sheets
Sheet1
Sheet2
Sheet3

In B4 enter: Max Value

In B5 enter & copy down:

=MAX(INDIRECT("'"&A5&"'!A2:A10"))

This calculates the max value from A2:A10 on each sheet. Adjust to suit.

Lets suppose that the following values are computed:

Max Value
39
40
40

In C4 enter: Rank

In C5 enter & copy down:

=RANK(B5,$B$5:$B$7)+COUNTIF($B$5:B5,B5)-1

In D1 enter:

=MAX(B5:B7)

In D2 enter: 1

which is N of Top N.

D3:

=MAX(IF(INDEX(B5:B7,MATCH(D2,C5:C7,0))=B5:B7,C5:C7))-D2

This calculates the number of ties the Nth value might have.

In D4 enter: Result List

In D5 enter & copy down:

=IF(ROW()-ROW(D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$7,MATCH(ROW()-ROW(D$5)+1,$C$5:$C$7,0)),"")

which produces:

Result List
Sheet2
Sheet3

Since the OP is on Excel 2003, A4:D7 can be converted into a List by
means of the Data|List option. This requires a different formula in C5:

=RANK(B5,$B$5:$B$8)+COUNTIF($B$5:OFFSET(B5,0,0),B5)-1

for the List functionality fails on progressive range expressions like
$B$5:B5. Hope MS attends to this matter.

The advantage is that every new sheet can be added without any concern
about the ranges the formulas apply to and copying down the formulas.
All this will happen automatically.

Hi Aladin!

I've just added this example to my files. Beautiful!

My Mac version of Excel has the List Manager feature, so I thought I'd
try your second option. Unfortunately, while the formulas get copied
down, the ranges don't adjust automatically.

I went through the steps where I choose each column, select Calculated
Column for Data Type, and entered their respective formulas, including
the OFFSET version of RANK.

Any ideas why the ranges don't adjust automatically, or is simply that
my version of Excel doesn't have that particular functionality?

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> Domenic,
>
> This can better be taken as a Top N problem...
>
> Let A4:A7 house the relevant sheet names, with the label Sheets in A4:
>
> Sheets
> Sheet1
> Sheet2
> Sheet3
>
> In B4 enter: Max Value
>
> In B5 enter & copy down:
>
> =MAX(INDIRECT("'"&A5&"'!A2:A10"))
>
> This calculates the max value from A2:A10 on each sheet. Adjust to suit.
>
> Lets suppose that the following values are computed:
>
> Max Value
> 39
> 40
> 40
>
> In C4 enter: Rank
>
> In C5 enter & copy down:
>
> =RANK(B5,$B$5:$B$7)+COUNTIF($B$5:B5,B5)-1
>
> In D1 enter:
>
> =MAX(B5:B7)
>
> In D2 enter: 1
>
> which is N of Top N.
>
> D3:
>
> =MAX(IF(INDEX(B5:B7,MATCH(D2,C5:C7,0))=B5:B7,C5:C7))-D2
>
> This calculates the number of ties the Nth value might have.
>
> In D4 enter: Result List
>
> In D5 enter & copy down:
>
> =IF(ROW()-ROW(D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$7,MATCH(ROW()-ROW(D$5)+1,$C$5:$C
> $7,0)),"")
>
> which produces:
>
> Result List
> Sheet2
> Sheet3
>
> Since the OP is on Excel 2003, A4:D7 can be converted into a List by
> means of the Data|List option. This requires a different formula in C5:
>
> =RANK(B5,$B$5:$B$8)+COUNTIF($B$5:OFFSET(B5,0,0),B5)-1
>
> for the List functionality fails on progressive range expressions like
> $B$5:B5. Hope MS attends to this matter.
>
> The advantage is that every new sheet can be added without any concern
> about the ranges the formulas apply to and copying down the formulas.
> All this will happen automatically.

Domenic,

On my Excel:mac 2001 on MacOS 9.x, with a List option under Insert...

This version reads the List range in my Windows Excel 2003 file as an
ordinary range. When I convert the range into a list using Insert|List,
the following is the case:

The formulas outside List range change upon inputting a new record. The
funny thing is: the formulas inside the range are not copied down!

Aladin

Domenic wrote:
> Hi Aladin!
>
> I've just added this example to my files. Beautiful!
>
> My Mac version of Excel has the List Manager feature, so I thought I'd
> try your second option. Unfortunately, while the formulas get copied
> down, the ranges don't adjust automatically.
>
> I went through the steps where I choose each column, select Calculated
> Column for Data Type, and entered their respective formulas, including
> the OFFSET version of RANK.
>
> Any ideas why the ranges don't adjust automatically, or is simply that
> my version of Excel doesn't have that particular functionality?
>
> In article <[email protected]>,
> Aladin Akyurek <[email protected]> wrote:
>
>
>>Domenic,
>>
>>This can better be taken as a Top N problem...
>>
>>Let A4:A7 house the relevant sheet names, with the label Sheets in A4:
>>
>>Sheets
>>Sheet1
>>Sheet2
>>Sheet3
>>
>>In B4 enter: Max Value
>>
>>In B5 enter & copy down:
>>
>>=MAX(INDIRECT("'"&A5&"'!A2:A10"))
>>
>>This calculates the max value from A2:A10 on each sheet. Adjust to suit.
>>
>>Lets suppose that the following values are computed:
>>
>>Max Value
>>39
>>40
>>40
>>
>>In C4 enter: Rank
>>
>>In C5 enter & copy down:
>>
>>=RANK(B5,$B$5:$B$7)+COUNTIF($B$5:B5,B5)-1
>>
>>In D1 enter:
>>
>>=MAX(B5:B7)
>>
>>In D2 enter: 1
>>
>>which is N of Top N.
>>
>>D3:
>>
>>=MAX(IF(INDEX(B5:B7,MATCH(D2,C5:C7,0))=B5:B7,C5:C7))-D2
>>
>>This calculates the number of ties the Nth value might have.
>>
>>In D4 enter: Result List
>>
>>In D5 enter & copy down:
>>
>>=IF(ROW()-ROW(D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$7,MATCH(ROW()-ROW(D$5)+1,$C$5:$C
>>$7,0)),"")
>>
>>which produces:
>>
>>Result List
>>Sheet2
>>Sheet3
>>
>>Since the OP is on Excel 2003, A4:D7 can be converted into a List by
>>means of the Data|List option. This requires a different formula in C5:
>>
>>=RANK(B5,$B$5:$B$8)+COUNTIF($B$5:OFFSET(B5,0,0),B5)-1
>>
>>for the List functionality fails on progressive range expressions like
>>$B$5:B5. Hope MS attends to this matter.
>>
>>The advantage is that every new sheet can be added without any concern
>>about the ranges the formulas apply to and copying down the formulas.
>>All this will happen automatically.

Aladin,

On my version of Excel, Excel:Mac v.X on the Mac OS X v.10.2.8, the List
option is also under Insert.

I just tried using the List Manager and, much to my surprise, found that
formulas outside the List range indeed change when entering a new
record. Wow!

This being the case, I don't understand why the formulas within the
range don't also adjust. Doesn't quite make sense to me. Nevertheless,
this still can prove to be very useful.

Interesting, though, that in your version, formulas inside the range
aren't copied down.

In article <[email protected]>,
Aladin Akyurek <[email protected]> wrote:

> Domenic,
>
> On my Excel:mac 2001 on MacOS 9.x, with a List option under Insert...
>
> This version reads the List range in my Windows Excel 2003 file as an
> ordinary range. When I convert the range into a list using Insert|List,
> the following is the case:
>
> The formulas outside List range change upon inputting a new record. The
> funny thing is: the formulas inside the range are not copied down!
>
> Aladin