Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying absolute formulas | Excel Worksheet Functions | |||
Copying DSUM formulas with absolute references | Excel Worksheet Functions | |||
Changing formulas from relative to absolute | Excel Discussion (Misc queries) | |||
Absolute values in formulas | Excel Discussion (Misc queries) | |||
Automatic absolute formulas | Excel Discussion (Misc queries) |