Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup
MDV MDV is offline
external usenet poster
 
Posts: 1
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
  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 833
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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2,203
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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
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   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 22,906
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   Report Post  
Posted to microsoft.public.excel.setup
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert a letter in front of number in every cell? [email protected] Excel Worksheet Functions 4 April 2nd 23 08:06 PM
trying to insert a 0 in front of zipcode hlander9 Excel Discussion (Misc queries) 2 January 13th 10 12:01 AM
Switching negative signs from back to front tracytracy123 Excel Discussion (Misc queries) 4 June 23rd 09 04:34 PM
Insert symbol in front of value Aggies Excel Discussion (Misc queries) 3 August 29th 07 07:46 PM
how to reference external refereces from a list FalconArt Excel Worksheet Functions 7 August 22nd 05 05:17 PM


All times are GMT +1. The time now is 12:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"