A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Setting up and Configuration
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 10th, 2010, 07:32 PM posted to microsoft.public.excel.setup
MDV
external usenet poster
 
Posts: 4
Default How to globally insert $ signs in front of all cell refereces?

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
Ads
  #2  
Old May 10th, 2010, 07:46 PM posted to microsoft.public.excel.setup
trip_to_tokyo[_3_]
external usenet poster
 
Posts: 932
Default How to globally insert $ signs in front of all cell refereces?

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.

  #3  
Old May 10th, 2010, 10:03 PM posted to microsoft.public.excel.setup
JLatham
external usenet poster
 
Posts: 1,896
Default How to globally insert $ signs in front of all cell refereces?

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

  #4  
Old May 10th, 2010, 10:04 PM posted to microsoft.public.excel.setup
Gord Dibben
external usenet poster
 
Posts: 20,252
Default How to globally insert $ signs in front of all cell refereces?

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


  #5  
Old May 10th, 2010, 10:06 PM posted to microsoft.public.excel.setup
Gord Dibben
external usenet poster
 
Posts: 20,252
Default How to globally insert $ signs in front of all cell refereces?

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.


  #6  
Old May 12th, 2010, 03:08 AM posted to microsoft.public.excel.setup
Matt's Dad
external usenet poster
 
Posts: 5
Default How to globally insert $ signs in front of all cell refereces?

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.