Automatically making formulas "Absolute" or "$ Relative"

J

JMATHES

If you were to type a formula in Cell A1 as follows:
=A2*A3

How do you automatically make this formula a relative formula (meaning add
the $ before and after column and row so it reads $A$2*$A$3) without having
to manually go into the cell and manually type the "$" sign each time?

Is there a way to tell the sheet to make all formulas relative?
 
E

Excel_Geek

When you're inputting the "=A1" (or whatever reference), you can hit the
F4 key and it will add the $s. Hit it again, and it will move through
all the variations you can have -- i.e. $A1, A$1, A1, $A$1, etc.

If you're inputing a formula like "=A1*A2", immediately after typing
(or clicking the) A1 is when you'd hit the F4 key to add $s to the A1.
After typing (or clicking the) A2, is when you hit the F4 key for it.
 
A

Alan

This code below in a normal VBA module will do it, you can then select the
range you want to convert and run the appropriate Macro.

Make a backup copy of your file and use the copy to do this,

Highlight and copy the code below,
Open your sheet and go Alt and F11 together to open the VBA editor,
Click 'Insert' on the top toolbar > 'Module'
In the new blank window, paste the code in, watch out for text wrap, there
should be eight separate lines of code in each Sub. The text wrap, if any in
the e-mail will be in the line beginning 'Cell.Formula = .......'
Go Alt and F11 again to close the VBA editor,

Save the backup copy of your file,

Highlight the sheet range you want to convert,
Go 'Tools' > 'Macro' > 'Macros', click the appropriate one and click 'Run'

Regards,
Alan.
PS This code isn't mine, I got it from one of the newsgroups a long time ago
but I cant remember who from. Many thanks whoever you were!


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

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
End If
Next
End Sub
 
M

Mike R

You can use f4 and it will allow you to toggle through all aspects of
"absolute" for the selected cell.

Mike Rogers
 
R

RagDyer

After you finish entering the formula, you can select the *entire* formula
in the formula bar, and then hit <F4>, and you'll change the *entire*
formula to absolute with just that *one* hit of <F4>.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Top