Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike
 
Posts: n/a
Default changing multiple cells from relative to absolute reference

Is it possible to change a large range of very complex formulas from relative
reference to absolute reference. Something other than the replace command?

thanks, Mike
  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Mike

one option that might work if the cell references you want to replace are
used multiple times and that is to use range names, for example if you have
the formulas
=A1*A2
=A1*A5
=A1+A10
and you now want to make A1 absolute, select A1, click in the name box (box
to left of formula bar above column A) and type a name for the range e.g.
Rng_A1
and press ENTER (v. important)
now choose

insert / name / apply and click ok

you will end up with
=Rng_A1*A2
=Rng_A1*A5
=Rng_A1+A10

and range names are absolute references.

Please test this on a copy of your workbook first to see if it achieves what
you want.

Cheers
JulieD


"Mike" wrote in message
...
Is it possible to change a large range of very complex formulas from
relative
reference to absolute reference. Something other than the replace command?

thanks, Mike



  #3   Report Post  
Chip Pearson
 
Posts: n/a
Default

You can do it with the following macro:

Sub ChangeFormulas()
Dim Rng As Range
For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
If Rng.HasArray Then
Rng.FormulaArray = Application.ConvertFormula( _
fromreferencestyle:=xlA1, Formula:=Rng.Formula,
toabsolute:=True)
Else
Rng.Formula = Application.ConvertFormula( _
fromreferencestyle:=xlA1, Formula:=Rng.Formula,
toabsolute:=True)
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Mike" wrote in message
...
Is it possible to change a large range of very complex formulas
from relative
reference to absolute reference. Something other than the
replace command?

thanks, Mike



  #4   Report Post  
Mike
 
Posts: n/a
Default

Chip, thanks for the macro, i'll give it a shot.

"Chip Pearson" wrote:

You can do it with the following macro:

Sub ChangeFormulas()
Dim Rng As Range
For Each Rng In Range("A1:A10").SpecialCells(xlCellTypeFormulas)
If Rng.HasArray Then
Rng.FormulaArray = Application.ConvertFormula( _
fromreferencestyle:=xlA1, Formula:=Rng.Formula,
toabsolute:=True)
Else
Rng.Formula = Application.ConvertFormula( _
fromreferencestyle:=xlA1, Formula:=Rng.Formula,
toabsolute:=True)
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Mike" wrote in message
...
Is it possible to change a large range of very complex formulas
from relative
reference to absolute reference. Something other than the
replace command?

thanks, Mike




  #5   Report Post  
Mike
 
Posts: n/a
Default

JulieD, that is a very good idea. Unfortunately, there are to many different
cells involved.

Mike


"JulieD" wrote:

Hi Mike

one option that might work if the cell references you want to replace are
used multiple times and that is to use range names, for example if you have
the formulas
=A1*A2
=A1*A5
=A1+A10
and you now want to make A1 absolute, select A1, click in the name box (box
to left of formula bar above column A) and type a name for the range e.g.
Rng_A1
and press ENTER (v. important)
now choose

insert / name / apply and click ok

you will end up with
=Rng_A1*A2
=Rng_A1*A5
=Rng_A1+A10

and range names are absolute references.

Please test this on a copy of your workbook first to see if it achieves what
you want.

Cheers
JulieD


"Mike" wrote in message
...
Is it possible to change a large range of very complex formulas from
relative
reference to absolute reference. Something other than the replace command?

thanks, Mike




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
How to determine which rows contain slected cells in a multiple c. Paul Excel Worksheet Functions 2 April 5th 05 05:07 PM
adding certain cells in multiple worksheets in multiple workbooks Stephen via OfficeKB.com Excel Worksheet Functions 1 February 4th 05 09:31 PM
how do i add the same text after current text in multiple cells Sue Excel Discussion (Misc queries) 3 January 13th 05 10:28 PM
Counting "rows", i.e. simultaneous criteria for multiple cells gkline Excel Worksheet Functions 2 November 19th 04 08:30 AM
How do I change an Excel range of cells from relative to absolute. Jrhenk Excel Worksheet Functions 2 November 15th 04 11:55 PM


All times are GMT +1. The time now is 06:04 PM.

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

About Us

"It's about Microsoft Excel"