Sorting lists with references

How do a sort a list that contains references/formulas?
(I want the reference to follow the row as it is "sorted around"..)

Excel does't seem to want to sort a list containg formulas, it will only sort lists with numerical values..

Thanks!

janfu,

If you want to sort based on the column of formulas, first copy the column
with the formulas, choose "Insert copied cells" from the right click menu,
then right click and choose paste special, values. That efectively doubles
the column with formulas, but with one as values. IF you want to sort based
on another column, skip this step.

Then choose your cells with the formulas and run this macro:

Sub FormulaToText()
Dim myCell As Range
On Error GoTo NoneFound
For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell
NoneFound:
End Sub

This will convert the formulas to strings, so that Excel won't update them.

Then do your sort based on your desired column of values. Then, reselect the
cells that have the formulas that were converted to text, and run this
macro:

Sub TransformToFormula()
Dim myCell As Range
On Error GoTo NoneFound
For Each myCell In Range("A1").SpecialCells(xlCellTypeConstants, 2)
myCell.Formula = myCell.Text
Next myCell
NoneFound:
End Sub

Then delete your extra column of values (if you had inserted them)

HTH,
Bernie
MS Excel MVP


"jonfu" <[email protected]> wrote in
message news:[email protected]...
>
> How do a sort a list that contains references/formulas?
> (I want the reference to follow the row as it is "sorted around"..)
>
> Excel does't seem to want to sort a list containg formulas, it will
> only sort lists with numerical values..
>
> Thanks!
>
>
> --
> jonfu
> ------------------------------------------------------------------------
> jonfu's Profile:
> http://www.excelforum.com/member.php...o&userid=28193
> View this thread: http://www.excelforum.com/showthread...hreadid=477281
>