`fixing´ a cell

[dude]

I use the form $C$6 to scale a column to cell C6

How can a HOTKEY be used when clicking on cell C6 to automatically
assign or insert the $sings? Please advise is this is called `fixing´


-dude

 

[Peo Sjoblom]

press F4

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[dude]

press F4

--

Regards,

Peo Sjoblom

thanks.. what is this operation properly called?
I could not find in HELP for lack of description.

-dude

 

[Alex Lawnmower]

Don't know if it has a official Microsoft name but it concerns toggling
("switching") between absolute and relative cell references.

I call this "Anchoring" because
1. the dollar sign reminds me of a ship's anchor ! and when you try to
explain the idea to someone non-techie, it is easier to understand than
"absolute and relative cell references".
2. that's what happens : the line or the column (or both) become "anchored"
or "fixed".

Please notice that the F4 key toggles between 4 different options.
If you press F4 once
the reference will become $A$1 : both the line and the column are anchored.
(That's absolutely absolute).

If you press F4 twice
the reference will become A$1 : only the line is anchored.
(That's relatively absolute).

If you press F4 three times
the reference will become $A1 : only the column is anchored.
(That's relatively absolute too).

If you press F4 four times
the reference will become A1 : you're back to the start, neither is
anchored.
(That's absolutely relative).

HTH
Alex

 

[dude]

Don't know if it has a official Microsoft name but it concerns toggling
("switching") between absolute and relative cell references.

I call this "Anchoring" because
1. the dollar sign reminds me of a ship's anchor ! and when you try to
explain the idea to someone non-techie, it is easier to understand than
"absolute and relative cell references".

2. that's what happens : the line or the column (or both) become "anchored"
or "fixed".

Oh.. thank you very much!! All I could find in the HELP was a
reference to Lotus 1-2-3 for abs/rel. The additional info below is
much appreciated because I use this function a LOT to scale
columns and whole arrays of data.

The tip is a real time-saver. And hard to find in reference material.


-dude

 

[Peo Sjoblom]

If you need to do this for more than once cell regularly you can use these
macros

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub

Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End Sub

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

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

put that in your personal macro workbook, then press Alt + F8 to pull up the
macros,
then double click on the particular macro you want to run (most likely Sub
ReltoAbs())

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



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[Peo Sjoblom]

Beware of line wrapping

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

the above should be on one line


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[dude]

Thanks.. although I use Text Editor macros a lot in maniuplating ASCII
files.. I haven't got to that stage in Excel as yet. Maybe one or two
simple macros to edit a worksheet.

Have bookmarked the link below and will starte with the Intro page.

-dude

 

[girthhart]

Peo,

Many thanks for macros, have saved me thousands of F4 keypresses.

Any way of incorporating within macros to ignore blank cells withi
selection and to convert Abs to Rel of several references in sam
formula e.g.

=$D$272+$D$274+$D$276 to =D272+D274+D27

 

[Gord Dibben]

girthart

Try these. Ignores cells without formulaws.

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


Gord Dibben MS Excel MVP