Automatically anchoring a range of selected cell

  • Thread starter Thread starter Mark
  • Start date Start date
M

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
 
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
 
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
 
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
 
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
 
Gord,

Thank you very much!

I had not seen that before.

Again, thank you!

Best regards,
Kevin
 
Back
Top