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

Question

I have a table of data in excel in sheet 1 which references various different cells in many other sheets. When I try to sort or filter the sheet, the references change when the cell moves. However, I don't want to manually go into each cell and insert $ signs everywhere, because some of the references are continuous, and I may want to auto-fill later.

For example, one of my data columns uses sheet 2, column B 23:28, sheet 2, column C 1:15, and some others. The section of the column which uses sheet 2's column B is all in the same place before sorting, as are each of the other sections. If I insert $ signs, I would no longer be able to insert empty rows and use auto-fill for new data.

In summary, I would like a way to keep my cell references static without using $ signs, but only for sorting/filtering purposes. My current workaround involves copying the values of the table, pasting in an empty sheet, and then sorting. I hope there's a better way to do this.

Answer 1

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.

Answer 2

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.