Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering / Dynamic Dashboard Question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering / Dynamic Dashboard Question
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. 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering / Dynamic Dashboard Question
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering / Dynamic Dashboard Question
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering / Dynamic Dashboard Question
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 C2:D2 (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" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering / Dynamic Dashboard Question
Cool! Thank you very much Max, this did what I needed!
"Max" wrote: 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 C2:D2 (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" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering / Dynamic Dashboard Question
Glad to hear. You're welcome.
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "stevedemo77" wrote in message ... Cool! Thank you very much Max, this did what I needed! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtering question | Excel Discussion (Misc queries) | |||
Bar Chart question for Dashboard presentation | Excel Discussion (Misc queries) | |||
Filtering a dynamic range | Excel Worksheet Functions | |||
Filtering question | Excel Discussion (Misc queries) | |||
Filtering Question | Excel Discussion (Misc queries) |