Looking for a way to generate the 'Top X' table listed below using formulas (not autofilters) to filter through and subtotal/rank the data. As you can see, the user will select the region, quarter, and year, and the result should be a ranking of Top X Branches for the specified time period.
Thanks!
Thanks!
Book1.xls | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | BRANCHID | Production$ | Region | Date | ||||||
2 | 134 | $82 | East | 4/1/07 | ||||||
3 | 134 | $47 | East | 9/18/07 | ShowTop3BranchIDsfor: | |||||
4 | 134 | $89 | East | 2/4/07 | ||||||
5 | 134 | $54 | East | 8/15/07 | Region: | East | ||||
6 | 687 | $33 | East | 8/16/07 | Quarter: | 3 | ||||
7 | 555 | $44 | East | 4/5/07 | Year: | 2007 | ||||
8 | 687 | $64 | East | 8/15/07 | ||||||
9 | 687 | $83 | East | 12/15/07 | TOP3 | |||||
10 | 898 | $81 | East | 7/17/07 | Rank | ID | Production | |||
11 | 898 | $87 | East | 3/11/07 | 1 | 687 | $180 | |||
12 | 789 | $87 | East | 2/2/07 | 2 | 134 | $101 | |||
13 | 789 | $11 | East | 3/20/07 | 3 | 898 | $81 | |||
14 | 546 | $54 | East | 1/22/07 | ||||||
15 | 546 | $38 | East | 12/1/07 | ||||||
16 | 138 | $45 | East | 6/11/07 | ||||||
17 | 123 | $11 | North | 2/10/07 | ||||||
18 | 123 | $88 | South | 1/8/07 | ||||||
19 | 123 | $87 | South | 8/7/07 | ||||||
20 | 123 | $57 | West | 9/8/07 | ||||||
21 | 123 | $15 | North | 12/3/07 | ||||||
22 | 700 | $211 | North | 12/19/07 | ||||||
23 | 700 | $47 | North | 5/13/07 | ||||||
24 | 794 | $65 | North | 4/18/07 | ||||||
DATA |