Sorting formulae

[Gerben Dirksen]

Hi,

I have the following problem. I would like to sort a list of linked
formulae, for example:
A B
1 Alice 5
2 Bob 3
3 Charles 8

but now I want to create a seperate list with the same entries, but
sorted differently, so first I create:
C D
1 =A1 =B1
2 =A2 =B2
3 =A3 =B3

but if I try to sort these they will stay in the same order. Of course
if I would type:
=A$1 =B$1
=A$2 =B$2
=A$3 =B$3

it would work, but is there a simple way to create this on a large
scale, I don't want to manually add 100s of $ signs.

Thanks for any help!

Gerben

 

[Bob Umlas]

Select all the cells; run this:
Sub cvtfml()
For Each x In Selection
x.Formula = Application.ConvertFormula(x.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub
Bob Umlas
Excel MVP

 

[Pete_UK]

Highlight the columns of formulae when they are in this format:

C D
1 =A1 =B1
2 =A2 =B2
3 =A3 =B3

then do Edit | Replace (or CTRL-H), with these settings:

Find What: A
Replace with: A$

Then click Replace all. Repeat so that you replace B with B$ - two
simple operations.

Hope this helps.

Pete