Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute References
Regarding a recent post "Subject: Changing all formulae in a worksheet from
absolute to relative" the solution was a Macro that made all the references relative or using Edit/Replace $/[blank]. Luckily for me (!) I want to make all the references in an array of cells (not all containing consisten formula) absolute so I can copy them to a further set of columns and then use edit/replace to point them at a different worksheet. You can do 'F4' for individual references but is there a way of doing it to all references in selected cells without having to run a macro? Thanks For any Help Dan B |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute References
Hi,
To go the other way I believe you need a macro. Right click the sheet tab, view code and paste this in. Change the range to suit:- Sub sistence_level_wages() Dim MyRange As Range Set MyRange = Range("A1:D100") ' Change to suit For Each cell In MyRange If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, xlAbsolute) End If Next End Sub Mike "Dan Beard" wrote: Regarding a recent post "Subject: Changing all formulae in a worksheet from absolute to relative" the solution was a Macro that made all the references relative or using Edit/Replace $/[blank]. Luckily for me (!) I want to make all the references in an array of cells (not all containing consisten formula) absolute so I can copy them to a further set of columns and then use edit/replace to point them at a different worksheet. You can do 'F4' for individual references but is there a way of doing it to all references in selected cells without having to run a macro? Thanks For any Help Dan B |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute References
Not without a macro.
Otherwise the F4 can be be used on one cell only. Gord Dibben MS Excel MVP On Mon, 15 Oct 2007 00:34:00 -0700, Dan Beard <Dan wrote: Regarding a recent post "Subject: Changing all formulae in a worksheet from absolute to relative" the solution was a Macro that made all the references relative or using Edit/Replace $/[blank]. Luckily for me (!) I want to make all the references in an array of cells (not all containing consisten formula) absolute so I can copy them to a further set of columns and then use edit/replace to point them at a different worksheet. You can do 'F4' for individual references but is there a way of doing it to all references in selected cells without having to run a macro? Thanks For any Help Dan B |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Absolute References
Thanks for your help - the Macro it is then. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute sell references | Excel Discussion (Misc queries) | |||
Absolute references in a cell | Excel Discussion (Misc queries) | |||
Absolute References | Excel Worksheet Functions | |||
replace absolute references | Excel Worksheet Functions | |||
replace absolute references | Excel Worksheet Functions |