Multiple reference locking/unlockng in formulas
 

Hi All,
I have several columns where i need to unlock or lock the references inside
formulas in each cell (removing "$"s from references). How do I perform a
massive action? Thanks.

Multiple reference locking/unlockng in formulas
 

Hi,

Select these columns then

Edit|Replace

In the 'Find what' box enter $

leave the other box empty and click 'Replace all'

For Excel 2007 the dialog is found in the 'Editing' group of the Home tab.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Hi All,
I have several columns where i need to unlock or lock the references inside
formulas in each cell (removing "$"s from references). How do I perform a
massive action? Thanks.

Multiple reference locking/unlockng in formulas
 

Mike thanks, but how do I do massive reference lock?

"Mike H" wrote:

Hi,

Select these columns then

Edit|Replace

In the 'Find what' box enter $

leave the other box empty and click 'Replace all'

For Excel 2007 the dialog is found in the 'Editing' group of the Home tab.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Hi All,
I have several columns where i need to unlock or lock the references inside
formulas in each cell (removing "$"s from references). How do I perform a
massive action? Thanks.

Multiple reference locking/unlockng in formulas
 

Hi,

Try this macro. To install it ALT+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code in. Select your data and
run the code, you will be prompted whether you want relative or absolute

Sub ChangeRef()
response = InputBox("Enter 1 for relative or 2 for absolute")
Select Case response
Case Is = 1
RefType = xlRelative
Case Is = 2
RefType = xlAbsolute
Case Else
Exit Sub
End Select
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, RefType)
End If
Next c

End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Mike thanks, but how do I do massive reference lock?

"Mike H" wrote:

Hi,

Select these columns then

Edit|Replace

In the 'Find what' box enter $

leave the other box empty and click 'Replace all'

For Excel 2007 the dialog is found in the 'Editing' group of the Home tab.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Hi All,
I have several columns where i need to unlock or lock the references inside
formulas in each cell (removing "$"s from references). How do I perform a
massive action? Thanks.

Multiple reference locking/unlockng in formulas
 

Thanks Mike. It worked just fine.

"Mike H" wrote:

Hi,

Try this macro. To install it ALT+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code in. Select your data and
run the code, you will be prompted whether you want relative or absolute

Sub ChangeRef()
response = InputBox("Enter 1 for relative or 2 for absolute")
Select Case response
Case Is = 1
RefType = xlRelative
Case Is = 2
RefType = xlAbsolute
Case Else
Exit Sub
End Select
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, RefType)
End If
Next c

End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Mike thanks, but how do I do massive reference lock?

"Mike H" wrote:

Hi,

Select these columns then

Edit|Replace

In the 'Find what' box enter $

leave the other box empty and click 'Replace all'

For Excel 2007 the dialog is found in the 'Editing' group of the Home tab.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Hi All,
I have several columns where i need to unlock or lock the references inside
formulas in each cell (removing "$"s from references). How do I perform a
massive action? Thanks.

Multiple reference locking/unlockng in formulas
 

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Thanks Mike. It worked just fine.

"Mike H" wrote:

Hi,

Try this macro. To install it ALT+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code in. Select your data and
run the code, you will be prompted whether you want relative or absolute

Sub ChangeRef()
response = InputBox("Enter 1 for relative or 2 for absolute")
Select Case response
Case Is = 1
RefType = xlRelative
Case Is = 2
RefType = xlAbsolute
Case Else
Exit Sub
End Select
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, RefType)
End If
Next c

End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Mike thanks, but how do I do massive reference lock?

"Mike H" wrote:

Hi,

Select these columns then

Edit|Replace

In the 'Find what' box enter $

leave the other box empty and click 'Replace all'

For Excel 2007 the dialog is found in the 'Editing' group of the Home tab.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Gladiator" wrote:

Hi All,
I have several columns where i need to unlock or lock the references inside
formulas in each cell (removing "$"s from references). How do I perform a
massive action? Thanks.