I am creating a weighted score card. All my formulas appear to work as expected, with the exception of the Rank formula.
Whenever I sort the sheet by either Team Member or Manager (both are setup as contact lists) the Rank function returns a #NO MATCH error for some of the rows.
Prior to sorting, the RANK formula is:
=RANKEQ([% of Best]@row, $[% of Best]$2:$[% of Best]$10)
After sorting by the Manager column, the formula changes to:
=RANKEQ([% of Best]@row, $[% of Best]$7:$[% of Best]$10)
The absolute reference range (rows 2 through 10) end up changing which causes the #NO MATCH error for some of the cells. The value 7 reflects the first row where the other manager's name starts after the sort.
Why is the absolute reference not retained?