automatically locked cells
 

How I can prevent my linking from one Excel file to another Excel file from
being locked? If I make one cell on file one €œ=€� to a cell on file two then
it automatically adds the €œ$€� to lock the cells. I have to go into each cell
and remove it to fill by dragging. i don't want this. If I can't prevent it,
can I remove them in a bulk way instead of having to go into each cell to
remove the "$"?

automatically locked cells
 

From worksheet press Ctrl+H to launch the 'Find and Replace' dialog

--Find What: $
--Replace With: (leave this blank)
--Hit Replace All

PS: For the first formula you can use F4 in edit mode and change it to
relative referencing before dragging

If this post helps click Yes
---------------
Jacob Skaria


"Garrett" wrote:

How I can prevent my linking from one Excel file to another Excel file from
being locked? If I make one cell on file one €œ=€� to a cell on file two then
it automatically adds the €œ$€� to lock the cells. I have to go into each cell
and remove it to fill by dragging. i don't want this. If I can't prevent it,
can I remove them in a bulk way instead of having to go into each cell to
remove the "$"?

automatically locked cells
 

OK, using the Ctrl H replaces "$" if it is displayed, not if it in a formula.
So do I have to go into every linked cell and highlight the formula to use F4
to remove the "$"? This is what I've been doing, but it very tedious as I'll
have 30 to 40 cells linked in a column that I want to drag across multiple
columns. Is it possible to highlight the first column and remove the "$" in
the formulas instead of each individual cell?

"Jacob Skaria" wrote:

From worksheet press Ctrl+H to launch the 'Find and Replace' dialog

--Find What: $
--Replace With: (leave this blank)
--Hit Replace All

PS: For the first formula you can use F4 in edit mode and change it to
relative referencing before dragging

If this post helps click Yes
---------------
Jacob Skaria


"Garrett" wrote:

How I can prevent my linking from one Excel file to another Excel file from
being locked? If I make one cell on file one €œ=€� to a cell on file two then
it automatically adds the €œ$€� to lock the cells. I have to go into each cell
and remove it to fill by dragging. i don't want this. If I can't prevent it,
can I remove them in a bulk way instead of having to go into each cell to
remove the "$"?

automatically locked cells
 

--Select the column and hit Ctrl+H
--Hit 'Options' in Find/Replace window.
--Make sure you select 'Formulas' in 'Look In'
--Find/Replace will replace $ for all formulas in that column

If this post helps click Yes
---------------
Jacob Skaria


"Garrett" wrote:

OK, using the Ctrl H replaces "$" if it is displayed, not if it in a formula.
So do I have to go into every linked cell and highlight the formula to use F4
to remove the "$"? This is what I've been doing, but it very tedious as I'll
have 30 to 40 cells linked in a column that I want to drag across multiple
columns. Is it possible to highlight the first column and remove the "$" in
the formulas instead of each individual cell?

"Jacob Skaria" wrote:

From worksheet press Ctrl+H to launch the 'Find and Replace' dialog

--Find What: $
--Replace With: (leave this blank)
--Hit Replace All

PS: For the first formula you can use F4 in edit mode and change it to
relative referencing before dragging

If this post helps click Yes
---------------
Jacob Skaria


"Garrett" wrote:

How I can prevent my linking from one Excel file to another Excel file from
being locked? If I make one cell on file one €œ=€� to a cell on file two then
it automatically adds the €œ$€� to lock the cells. I have to go into each cell
and remove it to fill by dragging. i don't want this. If I can't prevent it,
can I remove them in a bulk way instead of having to go into each cell to
remove the "$"?

automatically locked cells
 

When replacing, set options to lookin "Formulas"

If you want some macros to change references on selected cells......

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, 28 Sep 2009 21:56:02 -0700, Garrett
wrote:

OK, using the Ctrl H replaces "$" if it is displayed, not if it in a formula.
So do I have to go into every linked cell and highlight the formula to use F4
to remove the "$"? This is what I've been doing, but it very tedious as I'll
have 30 to 40 cells linked in a column that I want to drag across multiple
columns. Is it possible to highlight the first column and remove the "$" in
the formulas instead of each individual cell?

"Jacob Skaria" wrote:

From worksheet press Ctrl+H to launch the 'Find and Replace' dialog

--Find What: $
--Replace With: (leave this blank)
--Hit Replace All

PS: For the first formula you can use F4 in edit mode and change it to
relative referencing before dragging

If this post helps click Yes
---------------
Jacob Skaria


"Garrett" wrote:

How I can prevent my linking from one Excel file to another Excel file from
being locked? If I make one cell on file one “=” to a cell on file two then
it automatically adds the “$” to lock the cells. I have to go into each cell
and remove it to fill by dragging. i don't want this. If I can't prevent it,
can I remove them in a bulk way instead of having to go into each cell to
remove the "$"?