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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|