Sorting a Dynamic Indirect Function-CodePudding

I have the following function

=INDIRECT("'" & $C$4 &"'!"&CELL("address", C6))/100

where the cell in C4 has the name of another sheet (and is dynamic) and C6 is the location of data I want in that sheet. I then have a column of these where the row is incremented like:

A1=INDIRECT("'" & $C$4 &"'!"&CELL("address", C6))/100
A2=INDIRECT("'" & $C$4 &"'!"&CELL("address", C7))/100
A3=INDIRECT("'" & $C$4 &"'!"&CELL("address", C8))/100
.
.
.

And so I want to be able to sort by this row smallest to largest (largest to smallest as well) . Whenever I do this, it sorts other data in my table that is not an indirect reference correctly by the smallest to largest in this column, but the values in this column and all other columns with indirect references remain the same. Is there a way to solve this with a better written indirect function or sort function?

CodePudding user response：

You need to explicitly include the sheet in which the formulas reside in the reference passed to CELL.

Assuming this is Sheet1, change your first formula to:

=INDIRECT("'"&$C$4&"'!"&CELL("address",Sheet1!C6))/100

Now when you sort on this column the references will follow the values.