Ranking across worksheets

[Mark]

I receive a monthly report card on my employees, and i need to rank them
across multiple metrics. each worksheet represents an employee. is there a
way to rank cell N21 for example across 24 worksheets?

 

[Tom Hutchins]

You can use the RANK function across multiple sheets like this:

=RANK(N21,Sheet1:Sheet24!N21,1)

This formula could be entered in any cell (except N21) on every sheet where
you want to see the rank of N21 versus the other sheets. The final argument
tells Excel whether to rank in ascending or descending order. If it is 0
(zero) or omitted, Excel will rank N21 as if Sheet1:Sheet24!N21 were a list
sorted in descending order. If the final argument is nonzero, Excel will rank
N21 as if Sheet1:Sheet24!N21 were a list sorted in ascending order.

Hope this helps,

Hutch

 

[Mark]

Tom,

Perfect, that worked well.

How can i list the ranking order of employees on a new worksheet? I have
added an additional column of ranking info on everyone of the 24 worksheets,
is there a way for me to reference that data on a new worksheet, for instance:

Metric # 1
Employee 1 Rank 1
Employee 2 Rank 2
Employee 3 Rank 3

Metric # 2
Employee 1 Rank 1
Employee 2 Rank 2
Employee 3 Rank 3

The metric data and the employee name exist on each of the worksheets, i
have to imagine there is a way to do this...

Thanks again!!

 

[Tom Hutchins]

Sorry for the late reply - I have been trying lots of different ideas. It's
easy to pull in the metrics' values in descending order using LARGE, but much
harder to pull in the associated employee names. This is particularly true if
two or more employees ever have the same value for a metric. So far, the
simplest solution still looks like the best. On the new sheet, for each
metric:

- add a direct reference to the cell on each sheet that contains the
employee's name
- add a direct reference to the cell on each sheet that contains the
employee's value for that metric
- add a RANK formula to compute the rank of each employee's metric value
- sort the data for that metric by the RANK field

I have created a simple example workbook. You can download it here:
http://www.freefilehosting.net/download/3h785

Not fancy, but easy to do, avoids complicating factors like duplicate
values, and it works. Hope this helps,

Hutch