Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert selected formula references from relative to absolute
How do I convert a selected range of cell references within formulas from
relative to absolute? For example cells within a column of cells are set up as: ='sheet name'!B9 ='sheet name'!B10 etc. How do I convert or change all the references within the selected range of cells to: ='sheet name'!$B$9 ='sheet name'!$B$10 etc. Thank you, M |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert selected formula references from relative to absolute
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 Gord Dibben MS Excel MVP On Thu, 30 Oct 2008 08:59:02 -0700, M wrote: How do I convert a selected range of cell references within formulas from relative to absolute? For example cells within a column of cells are set up as: ='sheet name'!B9 ='sheet name'!B10 etc. How do I convert or change all the references within the selected range of cells to: ='sheet name'!$B$9 ='sheet name'!$B$10 etc. Thank you, M |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert selected formula references from relative to absolute
Hi Gord,
I'm new at this but would this also work in reverse from absolute to relative if you change the text "absolute" to "relative" in the Macro below? -- Regards Warren "Gord Dibben" wrote: 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 Gord Dibben MS Excel MVP On Thu, 30 Oct 2008 08:59:02 -0700, M wrote: How do I convert a selected range of cell references within formulas from relative to absolute? For example cells within a column of cells are set up as: ='sheet name'!B9 ='sheet name'!B10 etc. How do I convert or change all the references within the selected range of cells to: ='sheet name'!$B$9 ='sheet name'!$B$10 etc. Thank you, M |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert selected formula references from relative to absolute
Yes.
Here is the full set........................ 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 On Thu, 30 Oct 2008 09:30:15 -0700, Warren Easton wrote: Hi Gord, I'm new at this but would this also work in reverse from absolute to relative if you change the text "absolute" to "relative" in the Macro below? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert selected formula references from relative to absolute
Thanks for that.
-- Regards Warren "Gord Dibben" wrote: Yes. Here is the full set........................ 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 On Thu, 30 Oct 2008 09:30:15 -0700, Warren Easton wrote: Hi Gord, I'm new at this but would this also work in reverse from absolute to relative if you change the text "absolute" to "relative" in the Macro below? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert selected formula references from relative to absolute
Gord/ Warren and group - THANKS!!!
I sometimes spend 'hours' trying to figure 'it' out and finally give it up and ask - -Minutes- and you folks have the answer!! Thanks, M |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert selected formula references from relative to absolute
Hi, I have a reasonable, but limited knowledge of the advanced areas of
Excel. I want to do exactly as you have explained in these comments, but I don't understand what the response from Gord means, or what I do with it. I have multiple spreadsheets that I have linked and need to make a lot of relevant cells turn into absolute cells. Can anyway explain this to me in layman's terms? -- Thanks "M" wrote: Gord/ Warren and group - THANKS!!! I sometimes spend 'hours' trying to figure 'it' out and finally give it up and ask - -Minutes- and you folks have the answer!! Thanks, M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Relative (vs. Absolute) Cell References with macros | Excel Discussion (Misc queries) | |||
how to switch between relative and absolute references | New Users to Excel | |||
F4 key does not toggle relative/absolute cell references. | Excel Worksheet Functions |