Highest and Lowest on to other sheets

[Lensmeister]

Evening all,

Here is what I would like to achieve.

On Sheet called data I have a table of stations and a numbers year by year.

On sheet called Top10 I want the top highest stations.

On sheet called Bottom10 I want the lowest numbers.

I have attached a copy for clarity.

Thanks in advance.

[Ace_XL]

Try these

Top 10 sheet

Cell F2
=LARGE(INDEX(Data!$C$2:$R$19,,MATCH($B$3,Data!$C$1:$R$1,0)),D2)

Cell E2
=INDEX(Data!$B$2:$B$19,MATCH(F2,INDEX(Data!$C$2:$R$19,,MATCH($B$3,Data!$C$1:$R$1,0)),0))

Copy down

Bottom 10 sheet

Cell F2
=SMALL(INDEX(Data!$C$2:$R$19,,MATCH($B$3,Data!$C$1:$R$1,0)),D2)

Cell E2
=INDEX(Data!$B$2:$B$19,MATCH(F2,INDEX(Data!$C$2:$R$19,,MATCH($B$3,Data!$C$1:$R$1,0)),0))

Copy down

[CARACALLA]

Top 10


F2=LARGE(IF(Data!$C$1:$R$1='Top10'!$B$3,Data!$C$2:$R$19),'Top10'!$D2)

Control+shift+enter

copy down


E2=INDEX(Data!$B$2:$B$19,SMALL(IF(Data!$C$2:$R$19='Top10'!$F2,IF(Data!$C$1:$R$1='Top10'!$B$3,ROW(Data!$B$2:$B$19)-ROW(Data!$B$2)+1)),COUNTIF('Top10'!$F$2:'Top10'!F2,'Top10'!F2)))

Control+shift+enter

copy down

Bottom10

F2=SMALL(IF(Data!$C$1:$R$1='Top10'!$B$3,Data!$C$2:$R$19),Bottom10!$D2)

Control+shift+enter

copy down


E2=INDEX(Data!$B$2:$B$19,SMALL(IF(Data!$C$2:$R$19=Bottom10!$F2,IF(Data!$C$1:$R$1=Bottom10!$B$3,ROW(Data!$B$2:$B$19)-ROW(Data!$B$2)+1)),COUNTIF(Bottom10!$F$2:Bottom10!F2,Bottom10!F2)))

Control+shift+enter

copy down

[Lensmeister]

Pukka,

Thanks all for the help.

Soon as I get home tonight I will try these (unless I get 10 minutes free at work lol )