Why would the absolute reference change after sorting on a column?

sgintexas · 10/29/24

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?

jmyzk_cloudsmart_jp · 10/30/24

Hi @sgintexas and @Cory Page

The issue you're encountering with absolute references in Smartsheet is indeed perplexing and seems to be related to how Smartsheet retains cell references post-sorting. Unfortunately, this might be a limitation or bug within Smartsheet itself, as the software appears to adjust absolute references based on the sorting order.

While I don't have a direct fix for this behavior, I can suggest a more reliable workaround using the COLLECT function to dynamically capture the range based on conditions, which should remain stable regardless of how the data is sorted:

=IF(AND(Row@row >= 2, Row@row <= 10), RANKEQ([% of Best]@row, COLLECT([% of Best]:[% of Best], Row:Row, >=2, Row:Row, <=10)))

This formula ensures that the ranking is only applied to rows 2 through 10, irrespective of any sorting changes. COLLECT dynamically gathers the % of Best values within the specified rows, making the rank calculation robust against sorting.

Observations on Sorting and Absolute Ranges:

When sorted by Team Member: The absolute range dynamically adjusts to encompass the relevant data, which in your case changed to rows 6 to 9.
When sorted by Manager: Similar adjustment occurred, altering the range to rows 5 to 6.
When sorted by % of Best: The range modified to rows 7 to 10.

These observations suggest that Smartsheet recalculates the ranges based on the visible order of data post-sort, which can lead to unexpected behavior in formulas that rely on specific row indices.
Rather than maintaining a fixed reference to a specific row or set of rows, Smartsheet adjusts these references based on the new order of rows after a sort operation.

Smartsheet might be designed to work this way to ensure that formulas always operate on visible data as shown on the screen after sorting, which can be beneficial for certain dynamic reporting needs.

For a more stable solution, you might consider using the COLLECT function as demonstrated above or consult Smartsheet support to confirm if this behavior is intended or a known issue.

https://app.smartsheet.com/b/publish?EQBCT=da040d089f6c472c96dacc4d9bc199a3

After Team Member Sort

The absolute range changed to 6:9. (As Row 2 and 10 are now on Row 9 and 6)

After Manager Sort

The absolute range changed to 5:6. (As Row 2 and 10 are now on Row 5 and 6)

After % of Best Sort

The absolute range changed to 7:10. (As Row 2 and 10 are now on Row 7 and 10)

jmyzk_cloudsmart_jp · 11/08/24

Happy to help!😁 @sgintexas

Cory Page · 10/29/24

I am not able to post a fix but I can confirm that I too get the same type of issue, no matter what its not sticking to the absolute ranges. Even more odd is that the total size of the range is changing just like yours, maybe a bug?

jmyzk_cloudsmart_jp · 10/30/24