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 $