Absolute Reference on a Group of Cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ab - 05 Jun 2007 15:32 GMT

How can create an absolute reference on a group of cells

Reply to this Message

Chip Pearson - 05 Jun 2007 15:43 GMT

Use the '$' character. E.g., $A$1:$C$10

Note, though, this will change if you insert rows in 1:10. You can also use
INDIRECT("A1:C10") which prevents Excel from changing the reference when a
row in inserted.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

> How can create an absolute reference on a group of cells

Reply to this Message

Gord Dibben - 05 Jun 2007 15:56 GMT

For a range of cells you could use a macro to change all at once.

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

Gord Dibben MS Excel MVP

>How can create an absolute reference on a group of cells

Reply to this Message