How to globally insert $ signs in front of all cell refereces?


M

MDV

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
 
Ad

Advertisements

T

trip_to_tokyo

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.
 
G

Gord Dibben

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
 
Ad

Advertisements

M

Matt's Dad

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top