Formula translation on sort is similar to what we see in Excel and other similar traditional spreadsheet software’s.
Also in terms of workaround the same Excel traits apply here as well. To summarize, to avoid formula displacements on sort:
-
Have cell-references in formula as named-range variables. Details: Named Ranges
-
Use absolute references in formulas. Eg. =B$1+10
-
For entire column-range , write cell-ranges in formulas as
- SUM(A:A) - Refers all Column-A cells
- SUM(A:B) - Refers all cells in Columns - A & B.
-
Additionally, in SSDC, we recommend to set a header-row and divide the sheet into header-region and table-region. More details: The Table Header Row and Table Region
- Sort & Filter rules are applied only to table-region data. Hence, any aggregate formulas written in header-region are not affected by sorts.
- Only formulas with cell-references to adjacent cells on the same row & entire column-ranges ( i.e. A:A ) will have coherent behavior even on sort. Hence, recommend to avoid formulas with cross-row cell-references and cell-ranges in table region. These are bound to get affected on sort. Such formulas should ideally be moved to sheet header region.
-
Where all applicable, consider using SSDC specific OOB features like Relationships + Lookups/Rollups, Column-Formulas, Project-Management over traditional approaches like using VLOOKUP, auto-fill formulas etc. Using relationships++, formulas lock on related-row ( same sheet or cross-sheet ) and are not affected even when sheets are sorted and also bring performance gains.
-
Also request you to refer: Related post around best practices