Converting Cell References

[Guest]

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

 

[squenson via OfficeKB.com]

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

 

[miquele]

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...

 

[Guest]

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 $.

 

[ed]

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 $.






- Show quoted text -

Use BriSwy's method .

ed

 

[CC]

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

 

[CC]

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!

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[RobN]

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

 

[Gord Dibben]

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