Named Ranges

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I am trying to create a global named range (I have several
sheets with the same layout), but I cannot delete the
sheet reference in the Refer to.

Thanks for the help.

Keith
 
Keith, in the Refers to field, enter the cell reference like this: =!$A$1.
This will assign the name you define to that cell on every worksheet, as
well as on new worksheets.

Note a couple of limitations: (1) This name does not appear in the Name Box
on any worksheet, but it does appear in the Insert Name dialog box; and (2)
You can't use this name to refer to the corresponding cell on a different
worksheet, only to the cell on the current worksheet.
 
I would advise against using name references like =!$A$1

- serious bug: they give incorrect answers when any calculate method is
called from VBA
(they always refer to the active sheet)

- minor bug: the Trace precedents commands either crash Excel (XL97) or do
not work
(XL2002)

use =Indirect("$A$1") instead


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Charles, good points both. What I would advise then is to create "local"
names by defining them thus: Sheet1!myRangeName -- explicitly including the
worksheet name in the range name. Including the worksheet name allows me to
use the same name many times in the same workbook (because the worksheet
reference will be different). The name appears in the Name Box dropdown list
only on the worksheet on which it is defined, but it appears in the Define
Name dialog box (together with the name of the sheet it is defined on) on
all worksheets (another advantage over =!$A$1 name references). And you can
refer to it from any worksheet by including the sheet name:
=Sheet1!myRangeName.

Again, Charles, good points. Thanks for bringing them up.
 
Thank you, both. However, how can I get the name to show
up in Name Box dropdown list and have a global setting?

I have a file from a previous employee who had a global
name in the dropdown. In the Define window, the name
appears with the name of the sheet next to it. I don't
know how this was done, unless they did it manually in
each of the sheets.

Thanks again.

Keith
 
Keith, if the worksheet name appears next to the range name in the Define
Names dialog box, it's a local, not a global name. Local names appear in the
Define Names dialog box and in the Name Box dropdown list only on the sheet
on which they are defined. Global names appear in the Define Names dialog
box and the Name Box dropdown list on every sheet.

The easiest way to define a global name is to select the range and enter the
name directly in the Name Box. The easiest way to define a local name is to
select the range and enter the name directly in the Name Box using the
syntax:

Sheetname!RangeName

"In the Define window, the name appears with the name of the sheet next to
it. I don't know how this was done, unless they did it manually in each of
the sheets."

If you have a global name defined on a worksheet, and you copy that
worksheet (which they may have done), Excel copies the name along with the
worksheet, but makes a local name out of it, local to that copied worksheet.
 
Thanks, DDM.

I understand what you're saying, but for the Names with
the worksheet name next to it, I can be on any page and
select it from the Name Box dropdown list and it will
select that range. That's why I think it's global, but I
could be wrong in that the person actually created the
range on each sheet. That's probably what happened.

Thanks again.

Keith
 
Back
Top