top data based on rank

hello

my worksheet shows top 22 sales ppl and how they fare against a selected region.

the top 15 remain static (do not change), however the remaining 7 entries can change depending on the selected region's sales ppl.

issue:
i cant seem to figure out a way to show the sales ppl in the desired region to appear on the table. the "calculation worksheet" highlighted in yellow, shows the corresponding ppl for the selected region.

however, if for instance a person is ranked #2, and he falls in that region, i dont want to double count him.

the critia is based on the RANK of sales ppl.

can someone pls help!!!!

Do you need to list the Rank numbers as they appear in the PC Performance sheet?

If so, try in K65:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

And to get Name,

Please Login or Register  to view this content.

similarly for other column items.

I might have messed that up a bit. I didn't consider not repeating reps from above constant 15.

Try instead:

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

I am not sure I get it... so if the table has more than 24 people you want to Only display one name (the name in C10), is that correct?

hello nbvc

sorry for the confusion...if the table has less than 24 people, the person select from the drop down in I6 will fall in the table list....

however

if the # of ppl is greater than 24, and the person's rank is greater than 24, then...he wont show. im trying to show his rank compared to his peers...so in that situation...would it be possible to show him as the last entry?

hope you follow...thx u so much again!

Try this:

In D71:

=IF(ISNUMBER(C70),IF(ISNUMBER(MATCH(C10,$D$48:$D$70,0)),'PC Performance'!AS31,C10),"")

in C71:

=IF(D71="","",VLOOKUP(D71,'PC Performance'!AS8:AT194,2,0))

I think the other formulas in that row remain as they are...