Making multiple cells absolute at once

I set up a very large workbook. I have linked the formulas of one sheet to
another, however, I need the columns to be rows and the rows to be columns.
I have linked the values, but I need to make all the cells in the sheet
absolute before I can transpose them. There are over 20,000 cells and I
really don't want to hit the F4 key 20,000 times in the sheet. A replace
would not work for this, as the data is too big. I have tried a copy and
then paste special- paste link, but it only makes the reference absolute when
one cell is copied at a time. Any ideas on making many cell references
absolute at once?

have a look in vba help index for convertformula

--
Don Guillett
SalesAid Software
[email protected]
"Jamie A Miller" <[email protected](donotspam)> wrote in message
news:[email protected]...
> I set up a very large workbook. I have linked the formulas of one sheet
to
> another, however, I need the columns to be rows and the rows to be
columns.
> I have linked the values, but I need to make all the cells in the sheet
> absolute before I can transpose them. There are over 20,000 cells and I
> really don't want to hit the F4 key 20,000 times in the sheet. A replace
> would not work for this, as the data is too big. I have tried a copy and
> then paste special- paste link, but it only makes the reference absolute
when
> one cell is copied at a time. Any ideas on making many cell references
> absolute at once?

VBA is your only option.

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

On Thu, 19 May 2005 12:28:05 -0700, "Jamie A Miller"
<[email protected](donotspam)> wrote:

>I set up a very large workbook. I have linked the formulas of one sheet to
>another, however, I need the columns to be rows and the rows to be columns.
>I have linked the values, but I need to make all the cells in the sheet
>absolute before I can transpose them. There are over 20,000 cells and I
>really don't want to hit the F4 key 20,000 times in the sheet. A replace
>would not work for this, as the data is too big. I have tried a copy and
>then paste special- paste link, but it only makes the reference absolute when
>one cell is copied at a time. Any ideas on making many cell references
>absolute at once?

VB is the way to go, you could also use a find and replace parameter from the formula view. i.e find =B and replace with =$B$ of course depending on how
wide your spreadsheet is you will have to do this with the letters used in the columns.

I'm not sure why you are responding to a thread that is more than 11 years old, and was not started in this Forum.

Pete

Is there a way to delete?

that's a simplest, CTRL+F and replace $ by blank and do it twice.