Use of Named Ranges

B

Bill Allen

I'm having problems using named ranges.

What at first appeared to be a neat feature, it's now coming back to bite me
in the butt.

When I insert a worksheet into an existing workbook, if I have an existing
named range, I get the dialog box asking me if I want to use the existing
named range. If I say "yes", the ws value is incorrect because it will
reference the wrong value (the previously defined value). If I say "no", I
am asked to enter a new name. If I enter a new name, the expressions I have
in the ws I'm inserting will not work.

I guess named ranges work fine on free standing ws and workbooks, unless I'm
missing something.

If this is true, how can I replace the range name with the actual range
thereby removing reference to the named range?

If this is not true, what do I need to do different in order to use the
named ranges correctly?

Thanks,

Bill Allen
 
D

Dave Peterson

If you make your names sheet level names, then it might make it easier to use:

Insert|Name|define
Names in workbook: 'Sheet 999'!myNameHere


If you have formulas on the same worksheet, you can use:
=myNameHere

if you have formulas on other sheets that refer to that range, you can use:
='sheet 999'!mynamehere

And you can use the same name (myNamehere portion) on as many different sheets
as you want.

And to make working with names easier, get Jan Karel Pieterse's (with Charles
Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

=========

If you still want to get rid of the names...

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.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top