fix cell references

[Guest]

I often have to fix and then unfix cell references using the $ sign to fix
and then find and replace to unfix.

Is there a menu option to fix column / fix row / fix reference (and unfix).
If there is I cant find it.

 

[JulieD]

Hi AK

not AFAIK, however F4 is the shortcut key for this and takes you through a
cycle of
A1 - column & row relative
$A$1 - column & row absolute
A$1 - column relative & row absolute
$A1 - column absolute & row relative

would using a mixed absolute / relative reference solve your problem?

Cheers
JulieD

 

[Guest]

Thanks JulieD

Unfortunately F4 does something quite different for me (not sure what yet).

I do use mixed references. An example where it causes a problem is when
calculated a column percent with a fixed reference to a row - and then
copying the table to a new row.

 

[Gord Dibben]

AK

You must highlight the cell references in the formula bar before cycling
through with F4.

F2 or click in the formula bar to get into edit mode, then with the mouse,
select the cell refs and F4.


Gord Dibben Excel MVP