Change named range to "A1" style reference

  • Thread starter Thread starter mb
  • Start date Start date
M

mb

Hello,

When you select a cell in a formula that contains a named range, the name
of that range appears in the fumula. Is there a key (like 'F4') that
replaces the name with the cell reference?

Example: "= APPEL + EI" should be "=A1 + B1"

Any suggestions?

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