Question $ Symbol
 

Sorry, total newb question here but I lack the proper vocabulary so I'm
having a difficult time finding this answer in help or online. I have
several rather lengthy formulas that I've already entered. After
entering them, I've found I've made something of a major error in not
including the $ sign in front of the cell references. I now need to
copy the formulas and the auto re-assignment of the cell references is
just killing me. Editing them all manually will take ages. Is there
any way to automatically add the $ sign to all cell references in an
existing formula?


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=466761

Automatically making the references absolute is not possible AFAIK. However,
if the formulae you've copied are then unchanged, if you select the
absolutes in the first formula, then copy it.....

Forgive me if you already know this but $A$1 will remain the same, $A1 will
change row numbers with copying and A$1 will change columns.

--
Ian
--
"guilbj2" wrote in
message ...

Sorry, total newb question here but I lack the proper vocabulary so I'm
having a difficult time finding this answer in help or online. I have
several rather lengthy formulas that I've already entered. After
entering them, I've found I've made something of a major error in not
including the $ sign in front of the cell references. I now need to
copy the formulas and the auto re-assignment of the cell references is
just killing me. Editing them all manually will take ages. Is there
any way to automatically add the $ sign to all cell references in an
existing formula?


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=466761

click in the cell to edit. F4. Sample the effects on each cell reference
HTH

"Ian" wrote:

Automatically making the references absolute is not possible AFAIK. However,
if the formulae you've copied are then unchanged, if you select the
absolutes in the first formula, then copy it.....

Forgive me if you already know this but $A$1 will remain the same, $A1 will
change row numbers with copying and A$1 will change columns.

--
Ian
--
"guilbj2" wrote in
message ...

Sorry, total newb question here but I lack the proper vocabulary so I'm
having a difficult time finding this answer in help or online. I have
several rather lengthy formulas that I've already entered. After
entering them, I've found I've made something of a major error in not
including the $ sign in front of the cell references. I now need to
copy the formulas and the auto re-assignment of the cell references is
just killing me. Editing them all manually will take ages. Is there
any way to automatically add the $ sign to all cell references in an
existing formula?


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=466761

One cell at a time......

Highlight the address in the formula bar and hit F4 to cycle through the
options.

Many cells at a time..........

You would need VBA to make global changes to cell references.

Here are four........

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

Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 07:55:27 -0500, guilbj2
wrote:


Sorry, total newb question here but I lack the proper vocabulary so I'm
having a difficult time finding this answer in help or online. I have
several rather lengthy formulas that I've already entered. After
entering them, I've found I've made something of a major error in not
including the $ sign in front of the cell references. I now need to
copy the formulas and the auto re-assignment of the cell references is
just killing me. Editing them all manually will take ages. Is there
any way to automatically add the $ sign to all cell references in an
existing formula?