Dynamic Charts between Files

[nwarnoc1]

Hi,

I've been working with dynamic named ranges, charts and pivot tables.
They seem to work fine, as long as the source and chart are in the same
file. When they are in different files, I'm getting "Your formula
contains invalid external reference to a worksheet".

The data is horizontal, a la "Jan Feb Mar" with me adding new columns
to the right and capturing the last 12 mths.

When I have the ranges defined in the source file, I can click in the
Refers to field and it puts the marquee around the correct range. I can
also hit <F5> and put the range name in there and the correct range
gets selected.

However, when I click the series in the chart and try to put the range
names into the series formula, I get the error above.

The original series formula is
=SERIES('[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$8:$AT$8,1)

The named ranges look like:
Name: Months_RecCallOverallLabels:
Refers to:
=OFFSET(FIN_Months!$AA$7,0,COUNTA(FIN_Months!$AB$7:$IV$7)-11,1,12)

Name: Months_RecCallOverallBot:
Refers to:
=OFFSET(FIN_Months!$AA$7,1,COUNTA(FIN_Months!$AB$7:$IV$7)-11,1,12)

With the range names, it looks like:
=SERIES(Months_RecCallOverallLabels,Months_RecCallOverallLabels,Months_RecCallOverallBot,1)

I've tried it with and without replacing the name_ref argument.

I've tried it without the file references (although it escapes me how
they would find each other without that).

I've even tried defining the named ranged in the target file, hoping
the file name would allow that to work. Argh.

What am I missing?

Thanks!
Neva

 

[Jon Peltier]

You're missing the parent of the name. The name needs to be referenced with
a sheet or workbook name. Something like this:

=SERIES('[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallBot,1)

- Jon

 

[nwarnoc1]

OK, Jon,

I hope you don't mind a few details, because I know I'm just missing
something minor.

I've got the range names created in the file containing the charts,
"ch_RecentCall(Fin).xls".
The ranges "Refers To" look like:
=OFFSET('[05-CSAT-Tracker.xls]FIN_Months'!$AA$7,0,COUNTA('[05-CSAT-Tracker.xls]FIN_Months'!$AB$7:$IV$7)-11,1,12)

When I try to put that in the chart series formula, I get the "invalid
external reference to a worksheet" message.

The series formula looks like this when it fails:
=SERIES(Months_RecCallOverallLabels,Months_RecCallOverallLabels,Months_RecCallOverallBot,1)

I also tried it without the range name in the name_ref argument.

Neva

You're missing the parent of the name. The name needs to be referenced with
a sheet or workbook name. Something like this:

=SERIES('[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallBot,1)

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

Hi,

I've been working with dynamic named ranges, charts and pivot tables.
They seem to work fine, as long as the source and chart are in the same
file. When they are in different files, I'm getting "Your formula
contains invalid external reference to a worksheet".

The data is horizontal, a la "Jan Feb Mar" with me adding new columns
to the right and capturing the last 12 mths.

When I have the ranges defined in the source file, I can click in the
Refers to field and it puts the marquee around the correct range. I can
also hit <F5> and put the range name in there and the correct range
gets selected.

However, when I click the series in the chart and try to put the range
names into the series formula, I get the error above.

The original series formula is
=SERIES('[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$8:$AT$8,1)

The named ranges look like:
Name: Months_RecCallOverallLabels:
Refers to:
=OFFSET(FIN_Months!$AA$7,0,COUNTA(FIN_Months!$AB$7:$IV$7)-11,1,12)

Name: Months_RecCallOverallBot:
Refers to:
=OFFSET(FIN_Months!$AA$7,1,COUNTA(FIN_Months!$AB$7:$IV$7)-11,1,12)

With the range names, it looks like:
=SERIES(Months_RecCallOverallLabels,Months_RecCallOverallLabels,Months_RecCallOverallBot,1)

I've tried it with and without replacing the name_ref argument.

I've tried it without the file references (although it escapes me how
they would find each other without that).

I've even tried defining the named ranged in the target file, hoping
the file name would allow that to work. Argh.

What am I missing?

Thanks!
Neva

 

[nwarnock]

In case it's useful to anybody else, I'm going to outline my actual
steps to get it to work:

I had to
1. construct the range names without any file/sheet references at all
(and yes, in the chart file)
2. Modify the formula from
=SERIES('[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$9:$AT$9,1)


to

=SERIES('[05-CSAT-Tracker.xls]FIN_Months'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]FIN_Months'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]FIN_Months'!Months_RecCallOverallMid,1)


(So both file/sheet references are explicit in the series formula)

BUT

when I hit <enter>, it no longer looks like that! Instead, it reads as:
=SERIES('[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallMid,1)

That was challenging!

OK, Jon,

I hope you don't mind a few details, because I know I'm just missing
something minor.

I've got the range names created in the file containing the charts,
"ch_RecentCall(Fin).xls".
The ranges "Refers To" look like:
=OFFSET('[05-CSAT-Tracker.xls]FIN_Months'!$AA$7,0,COUNTA('[05-CSAT-Tracker.xls]FIN_Months'!$AB$7:$IV$7)-11,1,12)

When I try to put that in the chart series formula, I get the "invalid
external reference to a worksheet" message.

The series formula looks like this when it fails:
=SERIES(Months_RecCallOverallLabels,Months_RecCallOverallLabels,Months_RecCallOverallBot,1)

I also tried it without the range name in the name_ref argument.

Neva

You're missing the parent of the name. The name needs to be referenced with
a sheet or workbook name. Something like this:

=SERIES('[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallLabels,'[05-CSAT-Tracker.xls]'!Months_RecCallOverallBot,1)

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

Hi,

I've been working with dynamic named ranges, charts and pivot tables.
They seem to work fine, as long as the source and chart are in the same
file. When they are in different files, I'm getting "Your formula
contains invalid external reference to a worksheet".

The data is horizontal, a la "Jan Feb Mar" with me adding new columns
to the right and capturing the last 12 mths.

When I have the ranges defined in the source file, I can click in the
Refers to field and it puts the marquee around the correct range. I can
also hit <F5> and put the range name in there and the correct range
gets selected.

However, when I click the series in the chart and try to put the range
names into the series formula, I get the error above.

The original series formula is
=SERIES('[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$7:$AT$7,'[05-CSAT-Tracker.xls]FIN_Months'!$AI$8:$AT$8,1)

The named ranges look like:
Name: Months_RecCallOverallLabels:
Refers to:
=OFFSET(FIN_Months!$AA$7,0,COUNTA(FIN_Months!$AB$7:$IV$7)-11,1,12)

Name: Months_RecCallOverallBot:
Refers to:
=OFFSET(FIN_Months!$AA$7,1,COUNTA(FIN_Months!$AB$7:$IV$7)-11,1,12)

With the range names, it looks like:
=SERIES(Months_RecCallOverallLabels,Months_RecCallOverallLabels,Months_RecCallOverallBot,1)

I've tried it with and without replacing the name_ref argument.

I've tried it without the file references (although it escapes me how
they would find each other without that).

I've even tried defining the named ranged in the target file, hoping
the file name would allow that to work. Argh.

What am I missing?

Thanks!
Neva