Mass Relative to Absolute Reference Change

[kraljb]

Is there an easy way to do a mass change to a group of 500 cells tha
currently have relative references (i.e. =Sheet1!A1) making the
absolute references (i.e. =Sheet1!A2) without going into each cel
individually?

Thanks,
Joh

 

[AlfD]

Hi!

You've lost me. The absolute reference corresponding to Sheet1!A
would, in my book, be Sheet1!$A$1.
Is this what you want or are you, as your post suggests, wanting t
move them down a row?

If the former, judicious use of find (e.g. !A and replace with !$A an
similar devices) can work wonders.

Al

 

[kraljb]

I think there is a misunderstanding, what I have is a worksheet that i
actually just a summary of many other worksheets. However, when th
links were created they were made relative for ease in making th
sheet. However, now they need to be made absolute, such that th
summary page can be reorganized without losing the references.

So I currently have something like

=Sheet1!A1 =Sheet1!B1 ... =Sheet1!BA1
=Sheet1!A2 =Sheet1!B2 ... =Sheet1!BA2
...
=Sheet1!A100 =Sheet1!B100 ... =Sheet1!BA100

I neeed all of those references to change to:

=Sheet1!$A$1 =Sheet1!$B$1 ... =Sheet1!$BA$1
=Sheet1!$A$2 =Sheet1!$B$2 ... =Sheet1!$BA$2
...
=Sheet1!$A$100 =Sheet1!$B$100 ... =Sheet1!$BA$100

With a way that does not require me to go into each cell.

Thanks,
Joh

 

[AlfD]

Hi!

Then I commend you to Bastien Mensinck at www.asap-utilities.com wher
you will find a free download of a fine set of utilities including on
which changes "relativities" - absolutely.

Al

 

[Gord Dibben]

Easy way is through VBA macro

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 Dibben Excel MVP