Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jamie A Miller
 
Posts: n/a
Default 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?
  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

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"
(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?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
city, state, zip from a single cell to multiple cells wjs2002 Excel Discussion (Misc queries) 3 April 29th 05 07:02 AM
Data from multiple cells into one string. Adam Excel Discussion (Misc queries) 3 March 8th 05 07:51 AM
Find cells without multiple spacebars and format... BeSmart Excel Discussion (Misc queries) 2 January 28th 05 12:52 AM
background formatting across multiple cells Casper Excel Discussion (Misc queries) 0 November 26th 04 12:18 PM
How do I extract cells from multiple workbooks Trevor Excel Discussion (Misc queries) 1 November 25th 04 11:59 PM


All times are GMT +1. The time now is 08:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"