Removed names in formulas?

  • Thread starter Thread starter Marian
  • Start date Start date
M

Marian

Hi,

if I remove the name of a cell, which is used in a formula, given
formula does not change it back to the address - it returns #NAME?.

Does exist any code how to change it?

Thanx

Marian
 
Marian said:
Hi,

if I remove the name of a cell, which is used in a formula, given
formula does not change it back to the address - it returns #NAME?.

Does exist any code how to change it?

Thanx

Marian
Click to expand...

I don't know about code, but you can do it easily enough by using Replace
(on the Edit menu). Suppose you had named A1 as "cell1" and this name was
used in formulas. Use Replace to replace all occurrences of "cell1" with A1.
Then the name isn't used and you can delete it without it affecting
anything.

Even with code, you would have to change the formulas BEFORE deleting the
name. Once you have deleted the name, the relationship with the cell has
gone, so you cannot then correct the formulas.
 
Paul napsal(a):
I don't know about code, but you can do it easily enough by using Replace
(on the Edit menu). Suppose you had named A1 as "cell1" and this name was
used in formulas. Use Replace to replace all occurrences of "cell1" with A1.
Then the name isn't used and you can delete it without it affecting
anything.

Even with code, you would have to change the formulas BEFORE deleting the
name. Once you have deleted the name, the relationship with the cell has
gone, so you cannot then correct the formulas.
Click to expand...
Hi Paul,

it is not problem to replace one reference. But I have 240 references in
one Sheet and it would take long time.
 
Marian said:
Paul napsal(a):
Hi Paul,

it is not problem to replace one reference. But I have 240 references in
one Sheet and it would take long time.
Click to expand...

If you mean that you have 240 references to one name, you can replace all of
these at once using the Replace All button in Edit > Replace.

If you mean that you have 240 different names, then I agree this will take
some time.
 
Once you remove the name, there ain't no going back.

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

(oh, heck. It was pretty short.)

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.co.uk/mvp
 
Back
Top