This is a classic spreadsheet frustration, and it has a straightforward fix. The problem isn't the sort tool, it's relying on cell positions instead of relationships. When you write a formula that points to `B2` and sort the data, your total stays in row 2 even though the account it belongs to moved. The tool works exactly as designed, but the design assumes your data is static. For anyone who needs to reorganize, that assumption breaks everything.
The solution is to stop referencing cells by their grid address and start referencing them by their content. Functions like `SUMIF`, `SUMIFS`, `XLOOKUP`, or `FILTER` let you say "find this account number anywhere in the column and sum its matching values." That logic survives any sort, any filter, any reordering. The account number is the anchor, not the row number. Once you build that into your summary sheet, you can sort, shuffle, or slice the data without breaking a single total.
For the user who posted this, the pattern is simple. In Sheet 1, instead of a direct cell reference, use `=SUMIF(Sheet2!A:A, A2, Sheet2!B:B)` where column A holds the account numbers and column B holds the values to sum. That formula will follow account `A2` no matter where it lands after sorting. The same approach works for names, dates, or any identifier. It takes a few minutes to rewrite the formulas, but it saves hours of manual rechecking every time you reorganize.
This isn't a workaround. It's how relational data should be handled from the start. Spreadsheets that treat rows as permanent addresses are brittle. Spreadsheets that treat identifiers as the reference point are resilient. The sort tool is not the enemy, static thinking is. Update your formulas to match how you actually work, and you'll never have to choose between sorting your data and keeping it accurate.