Let's say I have 10 worksheets which have the results of 10 games (or
competitions). On a summary sheet, I want to find the 5 largest scores from
the competitions. LARGE assumes that the array I want to search is contiguous
(all on the same sheet). Is there a way to tell it to look at each of the 10
sheets and find the 5 largest scores? Perhaps its just a syntax thing and I'm
just not sure who to indicate what I need.

Thanks,
Steve

You can run LARGE over several sheets:

=LARGE(Sheet1:Sheet3!A1:C1,1)

will look at A1 thru C1 on sheets 1,2,3 and return the largest value
--
Gary''s Student - gsnu200826


"Steve Haack" wrote:

Let's say I have 10 worksheets which have the results of 10 games (or
competitions). On a summary sheet, I want to find the 5 largest scores from
the competitions. LARGE assumes that the array I want to search is contiguous
(all on the same sheet). Is there a way to tell it to look at each of the 10
sheets and find the 5 largest scores? Perhaps its just a syntax thing and I'm
just not sure who to indicate what I need.

Thanks,
Steve

This works:

With the number being in cell A1 on each sheet.

=LARGE(Sheet1:Sheet10!A$1,ROWS(A$1:A1))

Copy down for a total of 5 cells.

--
Biff
Microsoft Excel MVP


"Steve Haack" wrote in message
...
Let's say I have 10 worksheets which have the results of 10 games (or
competitions). On a summary sheet, I want to find the 5 largest scores
from
the competitions. LARGE assumes that the array I want to search is
contiguous
(all on the same sheet). Is there a way to tell it to look at each of the
10
sheets and find the 5 largest scores? Perhaps its just a syntax thing and
I'm
just not sure who to indicate what I need.

Thanks,
Steve

Thanks for the quicl response. Let me complicate things a bit more. Each of
the sheets that has compitition results has a different list of competitors
(it varies from time to time). On each sheet, I have defined a table with
each competitor and several data points.

I want to be able look at each table and extract the 5 BEST points scores
for each competitor.

I know how to lookup Player1's scores by using INDEX and MATCH, and I can do
that to get the data from each of the tables, but what is the best way to
then determine the 5 highest scores? I am trying to use tables, rather than
absolute cell references, due to the fact that they might move around or
change.

"T. Valko" wrote:

This works:

With the number being in cell A1 on each sheet.

=LARGE(Sheet1:Sheet10!A$1,ROWS(A$1:A1))

Copy down for a total of 5 cells.

--
Biff
Microsoft Excel MVP


"Steve Haack" wrote in message
...
Let's say I have 10 worksheets which have the results of 10 games (or
competitions). On a summary sheet, I want to find the 5 largest scores
from
the competitions. LARGE assumes that the array I want to search is
contiguous
(all on the same sheet). Is there a way to tell it to look at each of the
10
sheets and find the 5 largest scores? Perhaps its just a syntax thing and
I'm
just not sure who to indicate what I need.

Thanks,
Steve

I'm not sure what you're asking.

If you want to extract the 5 highest scores for each player from 10
different sheets I don't know how to do that. I would put all the data on
one sheet then we can figure it out.

--
Biff
Microsoft Excel MVP


"Steve Haack" wrote in message
...
Thanks for the quicl response. Let me complicate things a bit more. Each
of
the sheets that has compitition results has a different list of
competitors
(it varies from time to time). On each sheet, I have defined a table with
each competitor and several data points.

I want to be able look at each table and extract the 5 BEST points scores
for each competitor.

I know how to lookup Player1's scores by using INDEX and MATCH, and I can
do
that to get the data from each of the tables, but what is the best way to
then determine the 5 highest scores? I am trying to use tables, rather
than
absolute cell references, due to the fact that they might move around or
change.

"T. Valko" wrote:

This works:

With the number being in cell A1 on each sheet.

=LARGE(Sheet1:Sheet10!A$1,ROWS(A$1:A1))

Copy down for a total of 5 cells.

--
Biff
Microsoft Excel MVP


"Steve Haack" wrote in message
...
Let's say I have 10 worksheets which have the results of 10 games (or
competitions). On a summary sheet, I want to find the 5 largest scores
from
the competitions. LARGE assumes that the array I want to search is
contiguous
(all on the same sheet). Is there a way to tell it to look at each of
the
10
sheets and find the 5 largest scores? Perhaps its just a syntax thing
and
I'm
just not sure who to indicate what I need.

Thanks,
Steve