Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
city, state, zip from a single cell to multiple cells | Excel Discussion (Misc queries) | |||
Data from multiple cells into one string. | Excel Discussion (Misc queries) | |||
Find cells without multiple spacebars and format... | Excel Discussion (Misc queries) | |||
background formatting across multiple cells | Excel Discussion (Misc queries) | |||
How do I extract cells from multiple workbooks | Excel Discussion (Misc queries) |