Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Question $ Symbol
Sorry, total newb question here but I lack the proper vocabulary so I'm having a difficult time finding this answer in help or online. I have several rather lengthy formulas that I've already entered. After entering them, I've found I've made something of a major error in not including the $ sign in front of the cell references. I now need to copy the formulas and the auto re-assignment of the cell references is just killing me. Editing them all manually will take ages. Is there any way to automatically add the $ sign to all cell references in an existing formula? -- guilbj2 ------------------------------------------------------------------------ guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043 View this thread: http://www.excelforum.com/showthread...hreadid=466761 |
#2
|
|||
|
|||
Automatically making the references absolute is not possible AFAIK. However,
if the formulae you've copied are then unchanged, if you select the absolutes in the first formula, then copy it..... Forgive me if you already know this but $A$1 will remain the same, $A1 will change row numbers with copying and A$1 will change columns. -- Ian -- "guilbj2" wrote in message ... Sorry, total newb question here but I lack the proper vocabulary so I'm having a difficult time finding this answer in help or online. I have several rather lengthy formulas that I've already entered. After entering them, I've found I've made something of a major error in not including the $ sign in front of the cell references. I now need to copy the formulas and the auto re-assignment of the cell references is just killing me. Editing them all manually will take ages. Is there any way to automatically add the $ sign to all cell references in an existing formula? -- guilbj2 ------------------------------------------------------------------------ guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043 View this thread: http://www.excelforum.com/showthread...hreadid=466761 |
#3
|
|||
|
|||
click in the cell to edit. F4. Sample the effects on each cell reference
HTH "Ian" wrote: Automatically making the references absolute is not possible AFAIK. However, if the formulae you've copied are then unchanged, if you select the absolutes in the first formula, then copy it..... Forgive me if you already know this but $A$1 will remain the same, $A1 will change row numbers with copying and A$1 will change columns. -- Ian -- "guilbj2" wrote in message ... Sorry, total newb question here but I lack the proper vocabulary so I'm having a difficult time finding this answer in help or online. I have several rather lengthy formulas that I've already entered. After entering them, I've found I've made something of a major error in not including the $ sign in front of the cell references. I now need to copy the formulas and the auto re-assignment of the cell references is just killing me. Editing them all manually will take ages. Is there any way to automatically add the $ sign to all cell references in an existing formula? -- guilbj2 ------------------------------------------------------------------------ guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043 View this thread: http://www.excelforum.com/showthread...hreadid=466761 |
#4
|
|||
|
|||
One cell at a time......
Highlight the address in the formula bar and hit F4 to cycle through the options. Many cells at a time.......... You would need VBA to make global changes to cell references. Here are four........ 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 If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the above code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. Gord Dibben Excel MVP On Mon, 12 Sep 2005 07:55:27 -0500, guilbj2 wrote: Sorry, total newb question here but I lack the proper vocabulary so I'm having a difficult time finding this answer in help or online. I have several rather lengthy formulas that I've already entered. After entering them, I've found I've made something of a major error in not including the $ sign in front of the cell references. I now need to copy the formulas and the auto re-assignment of the cell references is just killing me. Editing them all manually will take ages. Is there any way to automatically add the $ sign to all cell references in an existing formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
The Mathematical "Implies" symbol | Excel Discussion (Misc queries) | |||
how to find and replace a symbol in my worksheet | Excel Discussion (Misc queries) | |||
I need a symbol but "symbol" in the Insert menu is grayed-out. | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |