Can't sort table with indirect called items

Question

Hello,

I have a table that collects cell values based on a reference cell that i want to have the ability to change name without breaking the table function. The problem is that when I try and sort values in Column 2 in ascending order it does nothing and the regular order is the same.

Would love some help in figuring out what could be the problem.

The function for the reference cell is just calling another cell-value with '=B4' for example.

The reference cell is for example D36 referencing another cell with '=B4'.
The sortcolumn all have the function '=INDIRECT("'"&$D188&"'!$K$36")'.

Answer 1

Hi Gusten,

Thank you for posting your question on the Microsoft Q&A forum.

Based on the information you shared, it appears that your table retrieves values from another worksheet using the INDIRECT function, while a reference cell stores the sheet name so that you can change the name without breaking the formulas. Below are some steps you can follow to help identify the cause of the sorting issue.

1/ Make sure the entire table is being sorted

• Click anywhere inside your table.
• Go to the Data tab in Excel.
• Click Sort.
• In the Sort window:
  • Select the column you want to sort.
  • Choose Smallest to Largest (ascending order).
  • Click OK.

If Excel prompts you to expand the selection, choose Expand the selection so the entire table is sorted together.

2/ Verify the formulas in the column you want to sort.

Please check whether each row in the column references different cells or the same cell as below:

• Click on the first cell in the column you want to sort that contains the formula.
• Look at the formula in the Formula Bar at the top of Excel.
• Move down to the next few rows and check the formulas there as well.
• Compare the formulas to see whether they reference different source cells.
• If several rows reference the same source cell, the returned values will be identical, and sorting the column will not change the order.

3/ Confirm that the values are numeric (if you are sorting numerically)

Sometimes the values returned from referenced cells are stored as text, even if they look like numbers. In that case, Excel may not sort them as expected. Please check:

• Click one of the cells in the column you want to sort
• Check the alignment of the value:
  • Numbers are typically right aligned.
  • Text values are typically left-aligned.
• Alternatively, you can also test this by entering the following formula in another column: =ISNUMBER(A2) (Replace A2 with the cell you want to test.)
• If the result is TRUE, the value is numeric.
• If the result is FALSE, the value is stored as text. If the values are stored as text, you may need to convert them to numbers in the source worksheet.

4/ Check whether the formulas reference fixed cells

If the formulas reference fixed cells such as $K$36, $K$37, sorting may appear ineffective because the formulas will always retrieve those exact cells from the source worksheet, regardless of the row order in the table.

Additionally, if you plan to sort the data frequently, it may be helpful to return values from a range instead of referencing individual fixed cells.

For example, you could use a formula such as: = INDEX(range, row_number)

This retrieves values from a column range dynamically and may work more smoothly when sorting within a table.

Or another option is to:

• Copy the column that contains the formulas.
• Use Paste Special > Values
• Sort the resulting values normally.

I hope the information above is helpful. If you have any further questions, please feel free to reach out.

---

If this answer resolves your issue, please click “Accept Answer” and consider upvoting it. If you have additional questions regarding this response, kindly click “Comment.”

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.