Absolute Reference
 

Is there to make all formulas in a row have absolute references without
going into each sell and putting $ signs in front of each column and row
indicator?

only way I know is if the row designation is unique in the cells (say
row is 1 and no other 1 in the cell formulas) select the row and change
"1" to $1"


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=277416

DME

Only through VBA macro.

Here are four...........

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

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

Gord Dibben Excel MVP

On Thu, 11 Nov 2004 10:05:00 -0600, "DME" <craigjoseathotmaildotcom wrote:

Is there to make all formulas in a row have absolute references without
going into each sell and putting $ signs in front of each column and row
indicator?

hi,
If you mean to bring the absolute value of cell H1 in a fomula in
A1:G1,then use $H$1 in the formula in cell A1.By dragging from cell A1 to
B1:G1,you may get the desired result.
Thank you,
K.S.Warrier

"DME" wrote:

Is there to make all formulas in a row have absolute references without
going into each sell and putting $ signs in front of each column and row
indicator?