Bill Manville wrote:
Ken Rock wrote:

When I use your suggested INDIRECT function, such as =INDIRECT(H2) I get
a #REF! error since cell H2 contains a formula



INDIRECT takes a string argument which is the address of the cell you want
to access the contents of.

=INDIRECT("H2") will give you the content of cell H2
=INDIRECT(H2) where H2 contains the value "A3" will give you the content of
cell A3.


Is there a way to alter the formula in each cell by adding the $ signs
using, perhaps, the Replace function without resorting to VBA?


Edit / Replace / = with =$ / Replace All will do the columns
Edit / Replace / A with A$ / Replace All will do references to column A
etc.

Or you could select all the cells and run the following macro

Sub MakeAbsolute()
Dim C As Range
For Each C In Selection.Cells
C.Formula = Application.ConvertFormula(C.Formula, xlA1, , True)
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks Bill,

I am now well on my way to master Super Sudoku. Next week, the world.....

Regards, Ken Rock