How change relative values to absolute on Mac

[somanyqs]

So simple, can't seem to find the answer
I have a block of cells and I need to change all formulas from relative to absolute

I've tried command-t, which doesn't do anything
Also tried using the "Switch Reference" in the ribbon, which seems to work only when I am IN one cell.
Any help would be great - thanks much!

 

[mikerickson]

When an address is selected in the formula bar, pressing Cmd+t will toggle through all four absolute/relative options for that address.

If you select all of a formula, pressing Cmd+t will change all cell referneces to the same abs/rel setting and then toggle through the four options.

I know of no built-in way to do it a whole block of cells at once. A macro to do this would be possible.

 

[mikerickson]

If your ultimate goal is to copy a cell with the formula =A1+$B$3 and paste it somewhere else and still have the formula =A1+$B$3, that is a different macro than the one I mentioned above and (IMO) a more useful one.

If that is the case, please advise.

 

[somanyqs]

Nope, looking to do it for a whole block of cells at once. Bummer the feature doesn't exist. Seemed like such an obvious one!
I'm not a big macro user on my Excel for Mac, but will look online to see if I can find a macro like this and how to use it -
Thank you

 

[mikerickson]

How about this macro. Select a range and call this macro repeatedly until the Rel/Abs is the way you want it.
NOTE: it will make all references the same
=A1+$B$3 will go to =$A$1+$B$3 to =A$1+B$3 to =$A1+$B3 to =A1+B3

Sub test()
    Static RA As Long
    Dim oneCell As Range
    RA = (RA Mod 4) + 1
    For Each oneCell In Selection
        With oneCell
            If .HasFormula Then
                .FormulaR1C1 = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlR1C1, RA, oneCell)
            End If
        End With
    Next oneCell
End Sub

 

[somanyqs]

Thanks much for the assistance-

 

[schalliol]

This is a dead thread, but I was trying to switch a block of cells this morning and, finding there was no good solution beyond a macro, a find and replace will sometimes work depending upon what you're doing. In my case, I was able to use the ! for a sheet reference to replace with !$ and because I only had two rows, I could search for 9 and replace with $9, etc. I just thought someone might find benefit to this.