Thanks Max, your method works, but here's the challenge. I have 40 dynamic
regions, and almost 1500 salespeople to choose from. Is there a way to
filter the 1500 rows of data by an off-sheet reference, such as the region
value in cell A1 of another sheet?

"Max" wrote:

Here's one formulas play which delivers it dynamically,
and caters for the possibility of ties in the sales as well

Assume source data is in sheet: x, cols A to C, where
col A = region, col B = Person, col C = total sales,
with data from row 2 down
(Its presumed that the col C figs are already the *total* sales for any
particular person, ie there's no need to further sum sales by person)

In your other sheet,
Assume your DV to select the region is in A1
Put in B2: =IF(x!A2=$A$1,x!C2-ROW()/10^10,"")
Put in C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(x!B:B,MATCH(L ARGE($B:$B,ROWS($1:1)),$B:$B,0)))
Copy C2 to D2. Select B2:D2, copy down to cover the max expected extent of
source data eg down to D100? Minimize/hide col B. Cols C and D will
auto-return the full list of persons & their total sales, sorted in
descending order by total sales. Just read-off the Top X as desired. Persons
with tied sales, if any, will be listed in the same relative order that they
appear within the source data.

Celebrate success, click the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"stevedemo77" wrote:
I am struggling with something that seems like it shouldn't be so
challenging. I have a worksheet that contains 3 levels of data...region,
person, and total sales dollars. I want to have a separate worksheet where I
can select the region from a drop down list (done), then have it present the
top 5 salespeople based on total sales dollars. There are anywhere from 10
to 30 salespeople for each region, but I only want to see the top 5. Is this
possible, and what would be the easiest way to accomplish this?
Thanks