Absolute formulas

[Rockbear]

Have a rather big sheet, and 25 of them that need to be set as absolute
formulas.IS there a way to change all formulas in column 1 to absolute

the formulas looks like this now : =sum(G138/9*12)
need it to be =sum($G$138/9*12)
and some just =sum($G138/9*12)
(the formulas are all like in each column, there are 354 rows in all 25
sheets)
Is it possible to mark the cells you want to change and do it??

PLEASE SAY YES, LOOL, OR THIS WILL BE A ALL NIGHTER
Have tried ctrl+b but did not find any way to do it

 

[Roger Govier]

Hi

From a previous posting by Gord Dibben

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


Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select the range of cells with your existing formulae
Alt+F8 to bring up Macros
Highlight the macro name
Run

 

[David Biddulph]

Next time, please don't throw in an unnecessary SUM function. Instead of
=sum(G138/9*12) you only need =(G138/9*12). Why not have a look at Excel
help to see what SUM does?

If you're going to throw in an unnecessary function, please give us some
variety and use
=MIN(G138/9*12) or
=MAX(G138/9*12) or
=MEDIAN(G138/9*12) or
=PRODUCT(G138/9*12) or
=AVERAGE(G138/9*12) or ...

Regular readers do get rather fed up with so many people using SUM as their
pointless function, and we would love a change. :-(

 

[Gord Dibben]

For those you would need a macro similar to the one Roger posted but for
Absolute Column only

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


Gord Dibben MS Excel MVP

 

[Roger Govier]

Sorry Gord
Didn't notice the requirement to fix column only.
I should have posted your sets of code in their entirety.