Reset Formula to Use Cell Address Not Cell Name
Reset Formula to Use Cell Address Not Cell Name
(OP)
I'm using Excel 2000 and have a workbook which contains defined names that refer to cell addresses. The workbook also contains formulas which use those names.
What I would like to do is delete the cell names without affecting the formulas where they are used. However, when I delete the cell name, the formulas do not update automatically and still look for the cell name.
Anyone know of a method to get the formulas to reset to using cell addresses rather than the cell name? I suppose that I could use find/replace but I was hoping for a method that required less user input.
What I would like to do is delete the cell names without affecting the formulas where they are used. However, when I delete the cell name, the formulas do not update automatically and still look for the cell name.
Anyone know of a method to get the formulas to reset to using cell addresses rather than the cell name? I suppose that I could use find/replace but I was hoping for a method that required less user input.





RE: Reset Formula to Use Cell Address Not Cell Name
RE: Reset Formula to Use Cell Address Not Cell Name
Obviously, if there are a large number of named cells, this could take a while....anyone know of a better option?
RE: Reset Formula to Use Cell Address Not Cell Name
RE: Reset Formula to Use Cell Address Not Cell Name
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm
Please let us know what works in the end...
RE: Reset Formula to Use Cell Address Not Cell Name
At the moment, this is more of an obsession than a dire need. I guess I'm remembering back when I first started using spreadsheets and Lotus 1-2-3 did update formulas to the cell address when you deleted the range name. In fact, Excel's help for Lotus 1-2-3 users says you can reset a range name by deleting it but the formula doesn't change.
I did get the save to *.wk1 to work on a test sheet so that is at least one, though somewhat less desirable option.
I did find on the Microsoft web site a Macro to Delete #REF! Defined Name Errors in a Workbook so there may be something with VBA that can be done.
The Name Manager utility looks interesting so I may give that a try. Might also try posting on one of the other Excel forums (exceltip.com, etc) and see what I get. I'll let you know if I find anything.
RE: Reset Formula to Use Cell Address Not Cell Name
Good luck!
RE: Reset Formula to Use Cell Address Not Cell Name
I did get what I was looking for from a post on excelforum.com so I thought I would post it here in case anyone was interested. The groups.google link below has some additional info about relative vs absolute address that may be of interest as well.
http://www.excelforum.com/showthread.php?s=&threadid=247747&highlight=reset+formula
Dave Peterson wrote
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%23FmAHA.2048%40tkmsftngp03
From: Jim Rech (jarech@kpmg.com)
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
RE: Reset Formula to Use Cell Address Not Cell Name
RE: Reset Formula to Use Cell Address Not Cell Name
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting