|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 24th, 2004, 08:06 PM
|
|||
|
|||
Making relative references absolute
Is there a way to 'wholesale' change a range of relative references to absolute. Im trying to copy relative references to a new location and it isnt working.
Thanks Shari 
|
|
#2
June 24th, 2004, 08:29 PM
|
|||
|
|||
|
Making relative references absolute
Shari... A reference in a formula i.e. A1 is relative If you use $A$1 is absolute, If you use A$1 then Row is absolute and column relative, all the way arround with $A1. While in a formula if the cursos is on a reference then pressing "F4" toggles through the varios absolute-relative types... Using names also is an absolute reference... Take a look at XL Help for Names... Hope this helps... Cheers. Juan -----Original Message----- Is there a way to 'wholesale' change a range of relative references to absolute. Im trying to copy relative references to a new location and it isnt working. Thanks Shari .
|
|
#3
June 24th, 2004, 08:37 PM
|
|||
|
|||
|
Making relative references absolute
Here is a macro from a previous time this question was asked.
http://tinyurl.com/39t86 "Shari" wrote in message ... Is there a way to 'wholesale' change a range of relative references to absolute. Im trying to copy relative references to a new location and it isnt working. Thanks Shari
|
|
#4
June 25th, 2004, 01:14 AM
|
|||
|
|||
|
Making relative references absolute
"Juan Sanchez" wrote...
A reference in a formula i.e. A1 is relative If you use $A$1 is absolute, If you use A$1 then Row is absolute and column relative, all the way arround with $A1. .... Reread the OP. It seems pretty clear the OP understands the difference but was just looking for a way of converting relative references in *existing* formulas to absolute.
|
|
#5
June 25th, 2004, 04:43 PM
|
|||
|
|||
|
Making relative references absolute
Thanks Juan
I am familiar with the reference types, but I had never seen the F4 trick. What I would like to do is select a range and change all from relative to absolute Shari "Juan Sanchez" wrote: Shari... A reference in a formula i.e. A1 is relative If you use $A$1 is absolute, If you use A$1 then Row is absolute and column relative, all the way arround with $A1. While in a formula if the cursos is on a reference then pressing "F4" toggles through the varios absolute-relative types... Using names also is an absolute reference... Take a look at XL Help for Names... Hope this helps... Cheers. Juan -----Original Message----- Is there a way to 'wholesale' change a range of relative references to absolute. Im trying to copy relative references to a new location and it isnt working. Thanks Shari .
|
|
#6
June 25th, 2004, 05:07 PM
|
|||
|
|||
|
Making relative references absolute
Still working on this?
|
|
#7
June 25th, 2004, 05:15 PM
|
|||
|
|||
|
Making relative references absolute
You would need a macro for that, I use 4 different macros,
press Alt + F11, click insertmodule and paste in Sub ReltoAbs() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute) Next End Sub Sub AbstoRel() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlRelative) Next End Sub Sub RelColAbsRows() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) Next End Sub Sub RelRowsAbsCol() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn) Next End Sub press Alt + Q to close the VBE, now select the formulas and run the first of them by pressing Alt + F8 and select the macro. If you want to make it available for future workbooks you can put the macro in your Personal.xls -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Shari" wrote in message ... Thanks Juan I am familiar with the reference types, but I had never seen the F4 trick. What I would like to do is select a range and change all from relative to absolute Shari "Juan Sanchez" wrote: Shari... A reference in a formula i.e. A1 is relative If you use $A$1 is absolute, If you use A$1 then Row is absolute and column relative, all the way arround with $A1. While in a formula if the cursos is on a reference then pressing "F4" toggles through the varios absolute-relative types... Using names also is an absolute reference... Take a look at XL Help for Names... Hope this helps... Cheers. Juan -----Original Message----- Is there a way to 'wholesale' change a range of relative references to absolute. Im trying to copy relative references to a new location and it isnt working. Thanks Shari .
|
|
#8
June 29th, 2004, 11:06 PM
|
|||
|
|||
|
Making relative references absolute
Tried your relto abs macro & got 'Compile error: Syntax Error. What am I doing wrong?
"Peo Sjoblom" wrote: You would need a macro for that, I use 4 different macros, press Alt + F11, click insertmodule and paste in Sub ReltoAbs() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute) Next End Sub Sub AbstoRel() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlRelative) Next End Sub Sub RelColAbsRows() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) Next End Sub Sub RelRowsAbsCol() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn) Next End Sub press Alt + Q to close the VBE, now select the formulas and run the first of them by pressing Alt + F8 and select the macro. If you want to make it available for future workbooks you can put the macro in your Personal.xls -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Shari" wrote in message ... Thanks Juan I am familiar with the reference types, but I had never seen the F4 trick. What I would like to do is select a range and change all from relative to absolute Shari "Juan Sanchez" wrote: Shari... A reference in a formula i.e. A1 is relative If you use $A$1 is absolute, If you use A$1 then Row is absolute and column relative, all the way arround with $A1. While in a formula if the cursos is on a reference then pressing "F4" toggles through the varios absolute-relative types... Using names also is an absolute reference... Take a look at XL Help for Names... Hope this helps... Cheers. Juan -----Original Message----- Is there a way to 'wholesale' change a range of relative references to absolute. Im trying to copy relative references to a new location and it isnt working. Thanks Shari .
|
|
#9
June 30th, 2004, 06:43 PM
|
|||
|
|||
|
Making relative references absolute
Montana
You are probably a victim of text-wrap. Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute) Should be all on one continuous line or have a continuation break(_) like Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, _ xlAbsolute) Gord Dibben Excel MVP On Tue, 29 Jun 2004 15:06:02 -0700, "Montana" wrote: Tried your relto abs macro & got 'Compile error: Syntax Error. What am I doing wrong? "Peo Sjoblom" wrote: You would need a macro for that, I use 4 different macros, press Alt + F11, click insertmodule and paste in Sub ReltoAbs() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsolute) Next End Sub Sub AbstoRel() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlRelative) Next End Sub Sub RelColAbsRows() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn) Next End Sub Sub RelRowsAbsCol() Dim Cell As Range For Each Cell In Selection Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn) Next End Sub press Alt + Q to close the VBE, now select the formulas and run the first of them by pressing Alt + F8 and select the macro. If you want to make it available for future workbooks you can put the macro in your Personal.xls -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Shari" wrote in message ... Thanks Juan I am familiar with the reference types, but I had never seen the F4 trick. What I would like to do is select a range and change all from relative to absolute Shari "Juan Sanchez" wrote: Shari... A reference in a formula i.e. A1 is relative If you use $A$1 is absolute, If you use A$1 then Row is absolute and column relative, all the way arround with $A1. While in a formula if the cursos is on a reference then pressing "F4" toggles through the varios absolute-relative types... Using names also is an absolute reference... Take a look at XL Help for Names... Hope this helps... Cheers. Juan -----Original Message----- Is there a way to 'wholesale' change a range of relative references to absolute. Im trying to copy relative references to a new location and it isnt working. Thanks Shari .
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Absolute cell references | Alan | Worksheet Functions | 1 | April 20th, 2004 03:50 PM |
| Changing Multiple Cells from Relative to Absolute Functions | Gord Dibben | Worksheet Functions | 1 | February 1st, 2004 09:18 PM |
| Howto: relative & absolute cell references | Eric Tubbs | Worksheet Functions | 3 | November 11th, 2003 09:36 PM |
| shortcut for copying a formula with relative references | Shawn | Worksheet Functions | 4 | October 11th, 2003 05:41 PM |
Linear Mode
