=LARGE and =INDEX with a filter or drop down box

  • Thread starter Thread starter assws
  • Start date Start date
A

assws

OK this may not even be possible but here it goes.

I have a very large sheet for sales. It has the sales persons name
state and than dollars sold.

I have been able (thanks to this forum) to find the top 10 sale
persons dollars and then the name of that person using.

=LARGE(c2:c1200,1) etc (in cell d2)

and than

{=INDEX(a$1:a$1200,LARGE(IF(c2:c1200=d2,ROW(c2:c1200)),COUNTIF(d$2:d2,d2)))}

Now the question is can I create a sheet using this same logic where
add either a drop down box or filter (or whatever) that will calc th
top 10 by state.

Since there are 50 possible states I dont want to create 50 sheets bu
I would like to click or enter "IL" and then the top 10 sales person
from Illinois would get displayed.

And for an added twist some sales persons could have sales in 2 o
more states so the STATE cell could be like IL, CA, NY

Thanks in advanc
 
Hi Assws!

Try using a pivot table for this sort of problem. You'll find a good
general introduction at:

http://peltiertech.com/Excel/Pivots/pivottables.htm

You may have to rearrange your data so that IL, CA, and NY are in
separate cells.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi
after creating the pivot table choose the mneu entry 'Pivot table' in
the pivot table toolbar. goto 'Sort and Top 10' and select your options

HTH
Frank
 
Back
Top