Absolute References


G

Guest

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
 
Ad

Advertisements

G

Guest

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
 
G

Gord Dibben

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top