From: column row To: $column$row
Posted by Matt on February 15, 2002 5:10 AM
I want to highlight a load of cells that currently have formulas in such as: " =a23+b34 " and i want to be able to select them all and make them all change to read: " =$a$23+$b$34 " i.e. I would like to know the function within Excel that will automatically put in the dollar sign in front of both elements of the cell reference. At the moment I'm going through each formula on the worksheet and manually putting in the dollar signs...
Posted by JohnG on February 15, 2002 5:52 AM
I think if you highlight the cells and press F4 it should do it for you.
Posted by Dave Harrison on February 15, 2002 5:52 AM
Hi Matt, to do this:
Click on the cell that contains the formula you wish to change.
Highlight the cell references you wish to change. (e.g. a23+b34 from your example)
Press F4, converts all to absolute references.
Keep pressing F4 and it toggles options, eg, both column and row, just row or just column.
HTH,
Dave.
Posted by IML on February 15, 2002 6:01 AM
I don't believe the f4 way works without activiating each cell. If your formalas all use similar columns, you may be able to get away with a couple of find (A and B) and replaces ($A$ and $b$). If your rows are the simlar items, you could find 23 and replace with $23. Then find = and replace with =$. same with +. This definately isn't a catch all, but could save you same time if all formulas have some sort of commonality.
good luck.
Posted by Matt (UK) on February 15, 2002 6:38 AM
Thanks for the suggestions on that one. The find and replace sort of worked, but i still have to check each cell formula to make sure its correct. Hence, i think i'll have to go through one by one pressing "F2 F4 Enter". Yipee.
Posted by Escalus on February 15, 2002 7:25 AM
Here's a macro .....
Sub ConvertFormulasToAbsolute()
Dim rng As Range, cell As Range
On Error GoTo e
Set rng = Selection.SpecialCells(xlFormulas)
For Each cell In rng
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, xlAbsolute)
Next
Exit Sub
e:
MsgBox "No formula cells in the selection."
End Sub
