ExcelBanter - Turn off Absolute references

Carolyn

Will a macro solution be OK with you?

In your case you would run the Sub Relative()

Select the entire sheet using CTRL +a(twice in 2003) then run the macro.

Ignores cells without formulas.

Sub Absolute()
'$A$1
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()
'A$1
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

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

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

Gord Dibben MS Excel MVP

On Thu, 2 Nov 2006 11:28:01 -0800, Carolyn
wrote:

I am working in a spreadsheet that has many formulas that are absolute
references. Is there a way to turn off all absolute formulas in a spreadsheet
at once? I am using Excel 2003, SP2. Thanks.

-- Carolyn

Gord Dibben MS Excel MVP