How do I sort a table in Excel if it has cell references in it?

Even with absolute references, sort does not handle references correctly. Relative references are made to point at the same relative offset from the new row location (which is obviously wrong because other rows are not in the same relative position) and absolute references are not changed (because the SORT omits the step of translating the absolute references after each rearrangement of a row). The only way to do this is to manually MOVE the rows (having converted references to absolute) one by one. Excel then does the necessary translation of references. The Excel SORT is deficient as it does not do this.

For me this worked like below -

I had sheet name references in formula for the same sheet. When I removed current sheet name from the formula and sorted it worked correctly.

You have a couple options:

(1) There's no way around cell references getting messed up when sorting when not using static references. The most basic way to deal with this is to simply copy and paste as values before sorting, which could be automated via a simple VBA macro.

(2) You could also try utilizing named ranges if you're using a number of common ranges across your formulas. You could define 'Sheet2!B23:28' as 'Range1' and reference 'Range1' within your formulas. In that case, sorting obviously wouldn't affect the range being specified since it's defined elsewhere.

HTH

I'm pretty sure this can be solved with the indirect() function. Here's a simplified spreadsheet:

            A         B                       C                         D    ...
       +------------------------------------------------------+- - - - - - - - -
     1 |CITY     |Q1-Q3 SALES|ANNUALIZED SALES:(Q1+Q2+Q3)*1.33|
       +======================================================+- - - - - - - - -
     2 |Tampa    | $23,453.00|                      $31,192.49|
       +------------------------------------------------------+
     3 |Chicago  | $33,251.00|                      $44,223.83|
       +------------------------------------------------------+
     4 |Portland | $14,423.00|                      $19,182.59|
       +------------------------------------------------------+
    ...|   ...   |    ...    |              ...               |

Normally the formula in cell C2 would be =B2*1.33, which works fine until you do a complex sort. To make it robust to sorting, build your own cell reference using the row number of that cell like this: =indirect("B"&row())*1.33.

Hope that works in your situation. It fixed a similar problem I was having.