Using $ key on both sides of column to lock

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need to lock multiple cells to move to different location on worksheet.......can multiple cells be locked using $ on both sides of column entry rather than entering each cell?
 
Joerolla said:
Need to lock multiple cells to move to different location on
Click to expand...
worksheet.......can multiple cells be locked using $ on both sides of column
entry rather than entering each cell?
I don't understand what you mean by "using $ on both sides of column entry".
When you see a cell reference such as $A$1 , this means that both column
letter and row number are absolute. The cell reference is made up of column
reference $A and row reference $1.

If you MOVE cells containing either relative or absolute references, you
will get the same results.
If you copy/paste, there will be a difference. The relative references will
adjust whereas the absolute ones will not.

If you still don't understand, post back with a specific query. Quote an
example formula and say exactly what you want to do and what formula you
want to result.
 
I have a column of numbers.......example $a$1 thru $a$30. I would like to know if there is a way to make these cells absolute without having to do each individual cell. Rather than $A$1 then $A$2 then $a$3....can I do all 30 at the same time with a formula or shortcut
 
Joerolla

I think what you are asking is can you change to Absolute references on more
than one cell at a time.

Only through VBA macro.

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlAbsolute)

''line above can be altered for varying references as below
''(cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
''(cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
''(cell.Formula, xlA1, xlA1, xlRelative)

End If
Next
End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben XL2002
 
Back
Top