While I am writing a formula, I move the cursor to a
cell that I want to reference in the formula. Usually,
that gives me a relative reference (e.g. A10), and I
can use F4 to cycle through alternative forms ($A$10,
A$10,$A10).

But if the referenced cell is named, Excel uses the
name for the reference, and F4 does not change it.

How can I replace the name with a relative reference
without having to enter it manually?

You could eliminate it all together
Insert Name Define
select the name that bothers you, and delete it from the defined list

" wrote:

While I am writing a formula, I move the cursor to a
cell that I want to reference in the formula. Usually,
that gives me a relative reference (e.g. A10), and I
can use F4 to cycle through alternative forms ($A$10,
A$10,$A10).

But if the referenced cell is named, Excel uses the
name for the reference, and F4 does not change it.

How can I replace the name with a relative reference
without having to enter it manually?

"ufo_pilot" wrote:
You could eliminate it all together Insert Name Define
select the name that bothers you, and delete it from
the defined list

It should be obvious that that is not what I want to do.
The cell is named for other purposes, of course.

Jim Rech posted a nice response at:
http://groups.google.com/groups?thre...%40tkmsftngp03

From: Jim Rech )
Subject: Can I "De-Name" Formula Cell References?
Newsgroups: microsoft.public.excel.misc, microsoft.public.excel
Date: 2001-02-16 13:32:51 PST

To do it to a cell or two first turn on Transition Formula Entry under
Tools, Options, Transition. Then go to the cell and press F2 and Enter.
When you turn off TFE the formula references should be de-named.

If you have a lot of cells to de-name select the range and run this macro:

Sub Dename()
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
For Each Cell In Selection.SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
ActiveSheet.TransitionFormEntry = False
End Sub

--
Jim Rech
Excel MVP

wrote:

While I am writing a formula, I move the cursor to a
cell that I want to reference in the formula. Usually,
that gives me a relative reference (e.g. A10), and I
can use F4 to cycle through alternative forms ($A$10,
A$10,$A10).

But if the referenced cell is named, Excel uses the
name for the reference, and F4 does not change it.

How can I replace the name with a relative reference
without having to enter it manually?

--

Dave Peterson