×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Reset Formula to Use Cell Address Not Cell Name
2

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.

RE: Reset Formula to Use Cell Address Not Cell Name

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).

RE: Reset Formula to Use Cell Address Not Cell Name

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?

RE: Reset Formula to Use Cell Address Not Cell Name

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

RE: Reset Formula to Use Cell Address Not Cell Name

2
(OP)
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.

RE: Reset Formula to Use Cell Address Not Cell Name

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!

RE: Reset Formula to Use Cell Address Not Cell Name

(OP)
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%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

You might try MathLook For Excel it is available for trial download at www.uts.com. It has a naming feature which should help you

RE: Reset Formula to Use Cell Address Not Cell Name

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources