Keeping links Static
 

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?

Keeping links Static
 

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


On Sun, 13 Dec 2009 07:36:01 -0800, caldog
wrote:

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?

Keeping links Static
 

Thanks for the reply will try this

"Gord Dibben" wrote:

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


On Sun, 13 Dec 2009 07:36:01 -0800, caldog
wrote:

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?

.

Keeping links Static
 

Worked great the first time-again thanks very much

"caldog" wrote:

Thanks for the reply will try this

"Gord Dibben" wrote:

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


On Sun, 13 Dec 2009 07:36:01 -0800, caldog
wrote:

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?

.

Keeping links Static
 

Gord Dibben <gorddibbATshawDOTca wrote...
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
....

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?