Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 30th 08, 03:59 PM posted to microsoft.public.excel.misc
m m is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 64
Default 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   Report Post  
Old October 30th 08, 04:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old October 30th 08, 04:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 81
Default 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   Report Post  
Old October 30th 08, 04:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old October 30th 08, 04:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 81
Default 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   Report Post  
Old October 30th 08, 05:19 PM posted to microsoft.public.excel.misc
m m is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 64
Default 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   Report Post  
Old March 24th 10, 01:09 AM posted to microsoft.public.excel.misc
j j is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 128
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert relative cell references to absolute cell references via amacro? Dave F[_2_] Excel Discussion (Misc queries) 1 May 15th 08 04:43 PM
Help with converting a block of cells with Absolute and mixed references to relative references Vulcan Excel Worksheet Functions 3 December 13th 07 11:43 PM
Relative (vs. Absolute) Cell References with macros Claude S. Weiller Excel Discussion (Misc queries) 1 August 3rd 06 08:56 PM
how to switch between relative and absolute references KeKe New Users to Excel 2 June 9th 06 05:23 PM
F4 key does not toggle relative/absolute cell references. java Excel Worksheet Functions 2 July 12th 05 09:53 PM


All times are GMT +1. The time now is 01:48 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017