Roger

For this you can use VBA macro(s)

Here are 4 macros. Use whichever you deem appropriate.

For your example, Sub Absolute() would be the one.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlRelative)
Next
End Sub


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 07:38:56 GMT, "Roger PB" wrote:

What is the simplest way of converting simultaneously such values to
($a$1:$a$100) etc.?

I could only find a slow and clumsy method by putting the formula in a row,
right dragging, then using F4 on each individual cell before using paste
special with transpose.

Roger PB