Show top 10 based on calculated cells

  • Thread starter Thread starter Ronald
  • Start date Start date
R

Ronald

I have a spreadsheet with pages for a number of sales people
Each page feed a status number (a sum based on the page data) to a column on
a front page.
Here the sales people are listed along with their status number.

I want to show the sales people ranked according to their score - like no. 1
has the highest status number, no. 2 has the second highest etc.

Now I update the sales data on the individual pages and make a manual sort
to get my list according to the latest status numbers.

Can I make the list "auto sort" based on changes in the data in the pages
for the individual sales people?

That way new sales data would automatically rearrange the list and e.g. put
a new sales person in the no. 1 position.
 
Ronald,

Add a new column, say B, with this formula in B1
=RANK($A$1:$A$50)
and copy down.

Simple descending sort of columns A & B based upon column B will show al;l
in order, not just top 10.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
another way (if you want to use formulas and bot autofilter)
If your sales person names are stored in column A and the score in
column B you can create a sorted list with formulas in column C and D.
enter the following in C1:
=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20+ROW($B$1:$B$20)/1000,ROW()),$B
$1:$B$20+ROW($B$1:$B$20)/1000,0))
enter this as array formula (CTRL+SHIFT+ENTER) and copy down
in D1 enter
=INDEX($B$1:$B$20,MATCH(LARGE($B$1:$B$20+ROW($B$1:$B$20)/1000,ROW()),$B
$1:$B$20+ROW($B$1:$B$20)/1000,0))
also as array formula -> copy down
 
Back
Top