Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

Once a reference is selected or the cursor is in it, pressing F4 one or more
time cycles to the four combinations.

billbrandi wrote:
Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

In versions previous to 2007:
go to "tools/options/view" and tick formulas tick box. it now will
show the formula text instead of the value. you can now use serach and
replace to eliminate the $'s.

no idea of 2007 yet...


squenson via OfficeKB.com wrote:
Once a reference is selected or the cursor is in it, pressing F4 one or more
time cycles to the four combinations.

billbrandi wrote:
Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

You can go to the Edit Menu and select Replace. In the "Find What:" box,
enter $. In the "Replace With:" box, leave it blank. With the cells
highlighted in your sheet where you would like to remove the absolute
references, click replace all. It will remove all of the $.

"billbrandi" wrote:

Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

On Jul 28, 6:32 pm, BriSwy wrote:
You can go to the Edit Menu and select Replace. In the "Find What:" box,
enter $. In the "Replace With:" box, leave it blank. With the cells
highlighted in your sheet where you would like to remove the absolute
references, click replace all. It will remove all of the $.



"billbrandi" wrote:
Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone- Hide quoted text -

- Show quoted text -

Use BriSwy's method .

ed

I need help with this same thing and noticed no one answered. Did you ever
resolve this issue?




"billbrandi" wrote:

Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

Actually what I need to do is put an absolute reference in all the cells
within a concatenated formula. I haven't found a way to do, even using
Search and Replace without updating each cell reference and the formula s
concatenating up to 7 cells. Too many to do manually. PLEASE HELP!


"billbrandi" wrote:

Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

This Macro removed all the dollar signs for me:

Sub Trial()
For Each cell In Selection
cell.Formula = Application.Substitute(cell.Formula, "$", "")
Next cell

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"CC" wrote in message
...
I need help with this same thing and noticed no one answered. Did you ever
resolve this issue?




"billbrandi" wrote:

Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one
keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

Maybe if you provide a sample of your formula; how it looks now and what you
want it to look like after the change, that may inspire someone to reply.

Rob

"CC" wrote in message
...
Actually what I need to do is put an absolute reference in all the cells
within a concatenated formula. I haven't found a way to do, even using
Search and Replace without updating each cell reference and the formula s
concatenating up to 7 cells. Too many to do manually. PLEASE HELP!


"billbrandi" wrote:

Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one
keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone

Here are a few macros for changing cell references.

Sounds like you need the last one for all rleative.

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)
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)
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)
Next
End Sub


Gord Dibben MS Excel MVP

On Sun, 23 Dec 2007 12:07:00 -0800, CC wrote:

I need help with this same thing and noticed no one answered. Did you ever
resolve this issue?




"billbrandi" wrote:

Is there a quick way to convert fixed cell references ($row$column) to
eliminate the $ without editing the formula and deleting the $ one keystroke
at a time? Sure would save me a lot of time.

Thanks
Bill Falzone