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 ,
Aladin Akyurek 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.