Is there a way to insert $ signs in front of certain cell characters on a
global basis?

I am facing having to click on each cell reference in a large spreadsheet
and hit F4 each time to insert the $ signs to anchor the cell reference. If
anyone knows of a way to change the entire spreadsheet to insert the $ signs
into each cell reference, I would very much appreciate hearing from you.

Thank you,
Mary

EXCEL 2007

"If anyone knows of a way to change the entire spreadsheet to insert the $
signs into each cell reference"

1. Click on, "cell" to left of letter A and above the number 1 to highlight
whole Worksheet.

2. Home tab / Font group / click on arrow in lower right hand corner /
Format Cells should launch / Number tab / Currency - in here select the
dollar sign that you want and hit OK.

3. Now type any number into any cell and it will be pre-fixed with the
dollar sign.

If my comments have helped please hit Yes.

Thanks.

If you are talking about making relative references like = A1 absolute
references like = $A$1 then take a look at:
http://www.ozgrid.com/VBA/formula-ref-change.htm


"MDV" wrote:

Is there a way to insert $ signs in front of certain cell characters on a
global basis?

I am facing having to click on each cell reference in a large spreadsheet
and hit F4 each time to insert the $ signs to anchor the cell reference. If
anyone knows of a way to change the entire spreadsheet to insert the $ signs
into each cell reference, I would very much appreciate hearing from you.

Thank you,
Mary

Pick from these 4 macros.

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


Gord Dibben MS Excel MVP

On Mon, 10 May 2010 11:32:01 -0700, MDV
wrote:

Is there a way to insert $ signs in front of certain cell characters on a
global basis?

I am facing having to click on each cell reference in a large spreadsheet
and hit F4 each time to insert the $ signs to anchor the cell reference. If
anyone knows of a way to change the entire spreadsheet to insert the $ signs
into each cell reference, I would very much appreciate hearing from you.

Thank you,
Mary

This helps to change cell references in what manner?


Gord Dibben MS Excel MVP

On Mon, 10 May 2010 11:46:01 -0700, trip_to_tokyo
wrote:

EXCEL 2007

"If anyone knows of a way to change the entire spreadsheet to insert the $
signs into each cell reference"

1. Click on, "cell" to left of letter A and above the number 1 to highlight
whole Worksheet.

2. Home tab / Font group / click on arrow in lower right hand corner /
Format Cells should launch / Number tab / Currency - in here select the
dollar sign that you want and hit OK.

3. Now type any number into any cell and it will be pre-fixed with the
dollar sign.

If my comments have helped please hit Yes.

Thanks.

The low-tech version that I use:

Ctrl-H to find and replace.
If you have =sumif(A3......
and want to change it to $A3
In the "Find What" field: (A
In the "Replace With" field: ($A

Not nearly as cool as the VB but a quick way to effect the change. I've
done it in spreadsheets with many thousands of formulas and haven't had a
problem. But you will want to make sure that you're selecting the right
"Find What" or you could create a bigger problem than just changing relative
to absolute references.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Pick from these 4 macros.

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


Gord Dibben MS Excel MVP

On Mon, 10 May 2010 11:32:01 -0700, MDV
wrote:

Is there a way to insert $ signs in front of certain cell characters on a
global basis?

I am facing having to click on each cell reference in a large spreadsheet
and hit F4 each time to insert the $ signs to anchor the cell reference.
If
anyone knows of a way to change the entire spreadsheet to insert the $
signs
into each cell reference, I would very much appreciate hearing from you.

Thank you,
Mary