Reset Formula to Use Cell Address Not Cell Name

[Eli]

I'm using Excel 2000 and have a workbook which contains defined name
that refer to cell addresses. The workbook also contains formula
which use those cell names.

What I would like to do is delete the cell names without affecting th
formulas where they are used. However, when I delete the cell name
the formulas do not update automatically and still look for the cel
name.

Anyone know of a method to get the formulas to reset to using cel
addresses rather than the cell name? I suppose that I could us
find/replace but I was hoping for a method that required less use
input.

I guess I'm remembering back when I first started using spreadsheet
and Lotus 1-2-3 did update formulas to the cell address when yo
deleted the range name. In fact, Excel's help for Lotus 1-2-3 user
says you can reset a range name by deleting it but the formula doesn'
change

 

[Dave Peterson]

If you change a setting first, you can. Or you could use a little macro to do
all the work.

But once you remove the name, there's no going back.

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

--
Jim Rech
Excel MVP

=============
And you didn't ask, but if you're working with names, you should have this addin
to make your life easier:

Jan Karel Pieterse, Charles Williams and Matthew Henson's utility called
NameManager.Zip from http://www.bmsltd.ie/mvp

 

[Eli]

Dave,

Thanks for the swift and great reply. As much as I searched to fin
some info, I don't think I would ever have thought of using "De-Name
as a search term.

I suspected the Transition Formula options might help but just couldn'
get it to work. Clicking on the Sheet Options help "?" for Transitio
Formula Entry, I now see where it says "makes names defined i
Microsoft Excel behave like names defined in Lotus".

I got both methods to work just fine and your caution about "no goin
back" is duly noted.

In my quest for a solution, I did pickup NameManager from anothe
recommendation. Though it didn't give me what I was looking for, I di
learn from it that cell names can be local or global. I also happene
across a macro on the Microsoft web site to Delete #REF! Defined Nam
Errors in a Workbook.

Anyway, it has been a learning experience but your reply is what I wa
looking for. Thanks again