Filtering / Dynamic Dashboard Question

[stevedemo77]

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.

 

[smartin]

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.

Try a pivot table. Put region in page area, person in row area, sum of
sales in data area. Double click the person button, advanced, top 10
auto show, show top 5 using field sum of sales.

 

[Max]

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(LARGE($B:$B,ROWS($1:1)),$B:$B,0)))
Copy C2 to D2. Select B22, 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]

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]

You mean the recalc performance?

For recalc efficiency ...

Put in B2: =IF(x!A2=$A$1,x!C2-ROW()/10^10,"")

you could copy just the criteria formula in B2 (above) down to cover the max
expected extent of source data, eg down to B1500. The criteria formula is the
only one which needs to cover the full extent of the source data.

As for the extract formulas in C22 (these are the "heavy" crunchers), just
copy down by say, 10 rows to D11? would probably suffice for your purpose of
determining the "Top 5", allowing for the possibility of a couple of
ties/multiple ties.

Above should do it ok for you.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[stevedemo77]

Cool! Thank you very much Max, this did what I needed!

 

[Max]

Glad to hear. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik