Hello everyone:

I am working on a spreadsheet where the values on one sheet in a workbook
are appear on a second sheet in the same workbook using the following formula:

=('Employee List'!A171)

The input sheet is called Employee List. I would like to "lock" the values
in this formula so that when I copy to another cell they do not change. I
know that when I do this it will look like this:

=('Employee List'!$A$171)

How do I copy this format to additional cells without manually entering the $

Hit 'F2' to edit the cell.
Hit 'F4' to toggle through Absolute (what you want), Relative
Column/Absolute Row, Absolute Column/Relative Row, Relative (what you have).

FYI, Below is a subroutine called 'FormulaReferences_AbsoluteRelative' that
will cycle through those options for all selected formulas.

'/=============================================/
' Sub Purpose: Toggle formulas in selected cells between
' All Absolute, Abs Row Relative Col, Relative Row Abs Col,
' and All Relative
'
' xlAbsolute = 1
' xlAbsRowRelColumn = 2
' xlRelRowAbsColumn = 3
' xlRelative = 4
'
Sub FormulaReferences_AbsoluteRelative()
Dim iToAbsolute As Integer
Dim iFromReferenceStyle As Integer
Dim rngCell As Range, rngSelection As Range
Dim strStatus_Cell As String

'check for an active workbook
If ActiveWorkbook Is Nothing Then
GoTo exit_Sub
End If

On Error Resume Next

'get previous value from registry
iToAbsolute = _
GetSetting(appname:="FormulaStatus", _
Section:="Status", key:="Value")
strStatus_Cell = _
GetSetting(appname:="FormulaStatus", _
Section:="Cell", key:="Value")

If Err.Number = 13 Then iToAbsolute = 0

On Error GoTo err_Sub

'check if 'cell' is same as current active cell
If strStatus_Cell = Selection.Cells(1).Address Then
Select Case iToAbsolute
Case 0
iToAbsolute = 1 'absolute
Case 1
iToAbsolute = 2 'xlAbsRowRelColumn
Case 2
iToAbsolute = 3 'xlRelRowAbsColumn
Case 3
iToAbsolute = 4 'Relative
Case 4
iToAbsolute = 1 'absolute
Case Else
iToAbsolute = 1 'absolute
End Select
Else
iToAbsolute = 1 'absolute
End If

Set rngSelection = _
Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))

'check for formulas, if none found, end procedure
If rngSelection Is Nothing Then
GoTo exit_Sub
End If

'add values to registry for next 'toggle'
SaveSetting appname:="FormulaStatus", _
Section:="Status", key:="Value", _
Setting:=iToAbsolute
SaveSetting appname:="FormulaStatus", _
Section:="Cell", key:="Value", _
Setting:=Selection.Range("A1").Address

'find out what reference style is being used
If Application.ReferenceStyle = xlA1 Then '1
iFromReferenceStyle = xlA1
Else
iFromReferenceStyle = xlR1C1 ' -4150
End If

For Each rngCell In rngSelection
rngCell.Formula = _
Application.ConvertFormula(Formula:=rngCell.Formul a, _
FromReferenceStyle:=iFromReferenceStyle, _
ToReferenceStyle:=iFromReferenceStyle, _
ToAbsolute:=iToAbsolute)
Next rngCell

exit_Sub:
On Error Resume Next
Set rngSelection = Nothing
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: FormulaReferences_AbsoluteRelative - " & Now()
Resume exit_Sub

End Sub
'/=============================================/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"nupe1493" wrote:

Hello everyone:

I am working on a spreadsheet where the values on one sheet in a workbook
are appear on a second sheet in the same workbook using the following formula:

=('Employee List'!A171)

The input sheet is called Employee List. I would like to "lock" the values
in this formula so that when I copy to another cell they do not change. I
know that when I do this it will look like this:

=('Employee List'!$A$171)

How do I copy this format to additional cells without manually entering the $