remove named ranges from formulas??

[Dave F]

I have a workbook that has a lot of formulas that use named ranges.
Is there a way to remove the named ranges and have the formulas show
conventional cell references?

Not sure if there is an option in Excel or this requires a macro? I'm
using XL 03.

Thanks.

 

[Shane Devenshire]

Hi,

Range names are good things, that said, if you delete the range names all
your formulas will return errors.

You can use the Replace command to replace all references to a name with its
address. With a lot of range names you should consider a VBA macro.

Cheers,
Shane Devenshire

 

[Dave Peterson]

I'd do this against a copy of the file...

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

====
Be aware that any reference to those names in your code will be broken.