Automatically anchoring a range of selected cell

[Mark]

When you have a range of cells which are referenced to
other cells within the work sheet (eg. "=b12"), is there a
short cut option which automatically anchors a range of
selected cells (ie. changes it to ="$B$12") rather then
having to go into individual cells???

Look forward to hearing your feedback

Mark

 

[Kevin Stecyk]

Hi Mark,

I am not sure I understand your question. I think you want to know if you
can change b12 to $B$12 easily. If you place your cursor on b12 in the
formula bar and press F4, you will see that B12 changes. Keep pressing F4
and it will cycle through the B12, $B$12, B$12, $B12 and then it repeats.

If I missed the mark, please let us know and someone will jump in.

HTH.

Regards,
Kevin

 

[Mark]

Wow, that's useful, but not exactly what I was after.

How can I easily do that for a range of cells that require
anhoring ... eg B1:B12

 

[Kevin Stecyk]

Mark,

I hope someone else jumps in because this isn't "clicking" for me.

What I would do is simply name B1:B2 by selecting B1:B2 and then Edit | Name
| Define and call it MarkRange (or something else). You can just refer to
MarkRange. For example, =sum(MarkRange) will sum B1:B2.

I don't know an easy way of making B1:B2 change to $B$1:$B$2 other than
selecting each cell and hitting the F4 key, or manually typing it out.

Regards,
Kevin

 

[Gord Dibben]

Macro to change relative to absolute.

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 XL2002

 

[Kevin Stecyk]

Gord,

Thank you very much!

I had not seen that before.

Again, thank you!

Best regards,
Kevin