Reset Formula to Use Cell Address Not Cell Name 2

[EGT01]

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.

 

[bltseattle]

Maybe you can save it to another file format that will drop the names and put in the addresses (I tried a few and it didn't work for me but maybe you will have better luck).

 

[TTK]

The only way I know how to do it is by slecting all of the cells in the sheet and using the Find/Replace command to maually change each name back to the cell reference.

Obviously, if there are a large number of named cells, this could take a while....anyone know of a better option?

 

[TTK]

It appears that saving it back to a WK1 format gets rid of the defined name in all referenced formulas, but you may lose a lot of other funtionality by saving it back .. so beware

 

[bltseattle]

A Google search turned up a Name Manager utility, free download I think, if you feel adventurous...

http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm

Please let us know what works in the end...

 

[EGT01]

Thanks for the responses. I haven't had much luck finding any info otherwise.

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.

 

[bltseattle]

I think the VBA approach could be worked out fairly easily - one interesting excel feature is the ability to dump the whole list of names and associated ranges into a worksheet (this would be step A) from the Insert>Name>Paste>Paste List menu. If you recorded a macro of the search/replace steps for one of the named ranges, and integrated it with a little loop routine to process the list generated in step A, it could be done. But what a pain in the butt for something that should/could be built-in functionality!

Good luck!

 

[EGT01]

I tried the Name Manager utility but it wasn't able to accomplish what I wanted. Although, it did seem to be a nice utility and I did learn from it that cell names can be either local or global.

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#FmAHA.2048@tkmsftngp03

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

 

[Gearhead]

You might try MathLook For Excel it is available for trial download at

http://www.uts.com.

It has a naming feature which should help you

 

[johnwm]

Be careful of Mathlook for Excel. I tried it and hated it and I couldn't get rid of it. The uninstaller still won't get rid of the Excel plug-in without re-installing Excel. A painful exercise just to try some buggy nagware!

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting