There are two levels of range names. One is workbook level; the name of
the range is only the name, and it is referenced from anywhere in the
workbook by the name. The other is sheet level; the name is preceded by
the sheet name (as in 'Sheet_Name'!Range2_Name).

When you define a sheet level name in code, you have to prepend the name
with the sheet name:

ActiveWorkbook.Names.Add Name:="'Sheet_Name'!Range_Name", RefersTo:= _
"=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' !$A:$A)-1,1)"

In a series formula, if you precede a workbook level name with the sheet
name, Excel knows what you meant to say, and changes the reference to
the workbook name. If it's a worksheet level name, Excel leaves it as is.

If you're not defining sheet level names, how did you get some
references in the series formula that Excel doesn't change from sheet
name to workbook name? If you have defined a workbook level name in a
worksheet, then make a copy of that sheet in the same workbook, the new
sheet has a sheet level name matching the workbook level name in the
original sheet. A global name "MyRange" that refers to Sheet1!$A$1 leads
to "'Sheet1 (2)'!MyRange" which refers to 'Sheet1 (2)'!$A$1.

If you do a lot of work with defined names, do yourself a favor and
download Jan Karel Pieterse's Name Manager, a free add-in at
http://jkp-ads.com.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

wdeleo wrote:

Greetings,

I am using the following to define some named ranges that are used for
charting:

ActiveWorkbook.Names.Add Name:="Range_Name", RefersTo:= _
"=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' !$A:$A)-1,1)"

After I define the names, I select a series and change the reference to the
named range. When I hit return, for some of the arguments for some of the
series, the sheet reference is changed to the name of the workbook and for
others it stays as the sheet ref (I don't see a pattern in how it behaves).

a)
=SERIES('Sheet_Name'!$E$1,'Sheet_Name'!Range1_Name ,'Workbook_Name.xls'!Range2_Name,4)

vs

b)
=SERIES('Sheet_Name'!$E$1,'Sheet_Name'!Range1_Name ,'Sheet_Name'!Range2_Name,4)

I'd rather have it just reference the sheet (as in "b") so I don't have to
worry about renaming the file and so forth (it seems to update automatically
but I don't want to worry that it may not, and the workbook names are rather
lengthy and cumbersome).

Can someone explain why it changes to the workbook from the sheet ref, and
how to set it to the sheet instead? Perhaps with my "Names.Add" statement?
RefersToLocal vs RefersTo???

Thanks so much

Thanks so much for your explanation and your time!!!




-----Original Message-----
There are two levels of range names. One is workbook
level; the name of
the range is only the name, and it is referenced from
anywhere in the
workbook by the name. The other is sheet level; the name
is preceded by
the sheet name (as in 'Sheet_Name'!Range2_Name).

When you define a sheet level name in code, you have to
prepend the name
with the sheet name:

ActiveWorkbook.Names.Add Name:="'Sheet_Name'!Range_Name",
RefersTo:= _
"=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' !
$A:$A)-1,1)"

In a series formula, if you precede a workbook level name
with the sheet
name, Excel knows what you meant to say, and changes the
reference to
the workbook name. If it's a worksheet level name, Excel
leaves it as is.

If you're not defining sheet level names, how did you get
some
references in the series formula that Excel doesn't
change from sheet
name to workbook name? If you have defined a workbook
level name in a
worksheet, then make a copy of that sheet in the same
workbook, the new
sheet has a sheet level name matching the workbook level
name in the
original sheet. A global name "MyRange" that refers to
Sheet1!$A$1 leads
to "'Sheet1 (2)'!MyRange" which refers to 'Sheet1 (2)'!
$A$1.

If you do a lot of work with defined names, do yourself a
favor and
download Jan Karel Pieterse's Name Manager, a free add-in
at
http://jkp-ads.com.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

wdeleo wrote:

Greetings,

I am using the following to define some named ranges
that are used for
charting:

ActiveWorkbook.Names.Add Name:="Range_Name", RefersTo:=
_
"=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' !
$A:$A)-1,1)"

After I define the names, I select a series and change
the reference to the
named range. When I hit return, for some of the
arguments for some of the
series, the sheet reference is changed to the name of
the workbook and for
others it stays as the sheet ref (I don't see a pattern
in how it behaves).

a)
=SERIES('Sheet_Name'!$E$1,'Sheet_Name'!
Range1_Name,'Workbook_Name.xls'!Range2_Name,4)

vs

b)
=SERIES('Sheet_Name'!$E$1,'Sheet_Name'!
Range1_Name,'Sheet_Name'!Range2_Name,4)

I'd rather have it just reference the sheet (as in "b")
so I don't have to
worry about renaming the file and so forth (it seems to
update automatically
but I don't want to worry that it may not, and the
workbook names are rather
lengthy and cumbersome).

Can someone explain why it changes to the workbook from
the sheet ref, and
how to set it to the sheet instead? Perhaps with
my "Names.Add" statement?
RefersToLocal vs RefersTo???

Thanks so much

.