Absolute formulas
 

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
--
Just a regular user

Absolute formulas
 

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
InsertModule
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
--
Regards
Roger Govier

"Rockbear" wrote in message
...

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
--
Just a regular user

Absolute formulas
 

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. :-(
--
David Biddulph


"Rockbear" wrote in message
...

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
--
Just a regular user

Absolute formulas
 

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

On Thu, 23 Oct 2008 04:58:00 -0700, Rockbear
wrote:

and some just =sum($G138/9*12)

Absolute formulas
 

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

--
Regards
Roger Govier

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 23 Oct 2008 04:58:00 -0700, Rockbear
wrote:

and some just =sum($G138/9*12)