de-apply names

[Don]

I want to replace the names in formulas with their cell references.
Basically, I want to undo the applying of a name. How can this be
accomplished?

The reason I need to undo names is that I applied some names that were
incorrectly defined as relative columns instead of absolute columns. This
caused a lot of unintended cell references to change to the applied name.
Although the spreadsheet seems to calculate correctly the applied name is
not appropriate. Therefore I need to change these names back to cell
references.

Thanks for any help
Don

 

[Pete_UK]

Have you tried using Find & Replace (CTRL-H) ? Select the offending
cells first.

Hope this helps.

Pete

 

[Dave Peterson]

I'd do this against a copy of the file...

Jim Rech posted a nice response at:
http://groups.google.com/groups?threadm=u3ZAo#FmAHA.2048@tkmsftngp03

From: Jim Rech ([email protected])
Subject: Re: 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