How to sort a table with only formulas?

  • Thread starter Thread starter Christian Borchgrevink-Lund
  • Start date Start date
C

Christian Borchgrevink-Lund

I have a table (several rows and columns) which I want to sort. All
cells are formulas which references to other cells outside the table I
want to sort. When trying to sort, Excel (both XP and Mac) sorts but a
split second later it goes back to original non-sorted.

Any suggestions? I could put the sheet on a webpage for you to see.

Christian
 
Christian,

Formulas are sorted based on their value at the time of the sort, but
then are reevaluated as if they were copied from the original cell to
the cell where they end up, which usually means they change.

You can either change all the cell references to absolute references,
which keeps the formulas, or you can copy/pastespecial values prior to
the sort, which would remove all the formulas. To keep the formulas
and change the cell references to absolute, select your table and run
the macro below.

HTH,
Bernie
MS Excel MVP

Sub ConvertToAbsoluteReferences()
Dim myCell As Range
Dim storedCalc As Variant
With Application
storedCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, xlAbsolute)
Next myCell
.ScreenUpdating = True
.EnableEvents = True
.Calculation = storedCalc
End With
End Sub
 
Thanks. That solved it!


Bernie Deitrick said:
Christian,

Formulas are sorted based on their value at the time of the sort, but
then are reevaluated as if they were copied from the original cell to
the cell where they end up, which usually means they change.

You can either change all the cell references to absolute references,
which keeps the formulas, or you can copy/pastespecial values prior to
the sort, which would remove all the formulas. To keep the formulas
and change the cell references to absolute, select your table and run
the macro below.

HTH,
Bernie
MS Excel MVP

Sub ConvertToAbsoluteReferences()
Dim myCell As Range
Dim storedCalc As Variant
With Application
storedCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, xlAbsolute)
Next myCell
.ScreenUpdating = True
.EnableEvents = True
.Calculation = storedCalc
End With
End Sub
Click to expand...
 
Well, not completely. The first time I sort the table it works. But the
second time, some values are placed completely wrong. The column from which
I sort is in the middle of the table...

Christian
 
Christian,

Send me a _small_ version of your spreadsheet - just enough rows to show the
problem - and I will take a look.

HTH,
Bernie
MS Excel MVP
 
Back
Top