Keeping links Static

  • Thread starter Thread starter caldog
  • Start date Start date
C

caldog

I have twenty pages of data, that I need to bring into a single page. The
data starts on line 137 of each and every page. Also I am only needing the
first four columns of each page. So what i have done is for the first page
is created a line to each line that I need in my summary page. Ex."
Sheet1!A137 and etc for the four columns.

What I want to do now is becasue this is already set up, I would like to now
do this Ex: Sheet1!$A$137, but do not want to do for each line and each
column that I have data in. I know that by hitting the F2 key and then the
F4 key that I can place the character where it needs to go.

Is there a way to globally do this step without having to do each cell
individually?
 
You can copy/paste this routine to a general module in your workbook then
run it on any range you select.

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


Gord Dibben MS Excel MVP
 
Thanks for the reply will try this

Gord Dibben said:
You can copy/paste this routine to a general module in your workbook then
run it on any range you select.

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


Gord Dibben MS Excel MVP




.
Click to expand...
 
Gord Dibben said:
You can copy/paste this routine to a general module in your workbook then
run it on any range you select.

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
Click to expand...
....

Tangent: OpenOffice Calc 3.1 can handle this using regular expressions
by replacing

\<([A-Z]+)([0-9]+)\>

with

\$$1\$$2

Wouldn't it be nice if Excel provided regular expressions?
 
Back
Top