Another Name question
 

Does anyone know of a way to reverse the name function and have all the
original cell references appear in the workbook, instead of the names? Just
wondering.

If you really, REALLY want to do that, work on a copy of your file and simply
delete all the names


"hk29" wrote:

Does anyone know of a way to reverse the name function and have all the
original cell references appear in the workbook, instead of the names? Just
wondering.

Hi Duke,

The problem with that is that it doesn't revert the formula to cell
references, it leaves the name and gives a #REF. Worst of all, any trace of
the name is now lost.

Bob


"Duke Carey" wrote in message
...
If you really, REALLY want to do that, work on a copy of your file and
simply
delete all the names


"hk29" wrote:

Does anyone know of a way to reverse the name function and have all the
original cell references appear in the workbook, instead of the names?
Just
wondering.

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

hk29 wrote:

Does anyone know of a way to reverse the name function and have all the
original cell references appear in the workbook, instead of the names? Just
wondering.

--

Dave Peterson

Interesting approach! I wonder if the OP could solve his problem by changing
the Formula Entry setting, then use Edit/Replace to replace an equal sign with
an equal sign, thus effectively re-entering all formulas.


On Thu, 21 Apr 2005 15:15:18 -0500, Dave Peterson
wrote:

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

hk29 wrote:

Does anyone know of a way to reverse the name function and have all the
original cell references appear in the workbook, instead of the names?
Just
wondering.

I just tried this approach on a workbook where I have assigned names to
columns (say Jan) and names to rows (say Sales), then used implicit
intersection, =Jan Sales, to refer to a single value. Replacing = with = or
editing the cell does not change the formula in this case (Excel XP).

On Thu, 21 Apr 2005 21:28:25 -0500, Myrna Larson
wrote:

Interesting approach! I wonder if the OP could solve his problem by changing
the Formula Entry setting, then use Edit/Replace to replace an equal sign
with
an equal sign, thus effectively re-entering all formulas.


On Thu, 21 Apr 2005 15:15:18 -0500, Dave Peterson
wrote:

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

hk29 wrote:

Does anyone know of a way to reverse the name function and have all the
original cell references appear in the workbook, instead of the names?
Just
wondering.