I have a chart which uses source data from two worksheets (three sets of
values - two sets from one sheet, one from the other). I have set up named
ranges for the values and x-axis but when inputting 3rd set of values name
into values field of chart source data dialog box i get "invalid external
reference to worksheet" error. The first two sets of values work fine. I have
2 named x-axis ranges, one for each sheet, plus two period select named
ranges (countif(range, "<="&cellwith current month)) one for each sheet.

Unfortunately I cannot collate the ranges into one sheet as the format of
the report cannot be changed.

Any ideas?

Thanks

pb

Hi,

Show us the formulas in your defined names for all three names - ie. the
Refers to contents.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"PBcorn" wrote:

I have a chart which uses source data from two worksheets (three sets of
values - two sets from one sheet, one from the other). I have set up named
ranges for the values and x-axis but when inputting 3rd set of values name
into values field of chart source data dialog box i get "invalid external
reference to worksheet" error. The first two sets of values work fine. I have
2 named x-axis ranges, one for each sheet, plus two period select named
ranges (countif(range, "<="&cellwith current month)) one for each sheet.

Unfortunately I cannot collate the ranges into one sheet as the format of
the report cannot be changed.

Any ideas?

Thanks

pb

Are you using all three ranges in the Y values input box for a single
series? If so, the problem is that data for Y values can only come from one
sheet. X values can also only come from one sheet, but it can be a different
sheet than the Y values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"PBcorn" wrote in message
...
I have a chart which uses source data from two worksheets (three sets of
values - two sets from one sheet, one from the other). I have set up named
ranges for the values and x-axis but when inputting 3rd set of values name
into values field of chart source data dialog box i get "invalid external
reference to worksheet" error. The first two sets of values work fine. I
have
2 named x-axis ranges, one for each sheet, plus two period select named
ranges (countif(range, "<="&cellwith current month)) one for each sheet.

Unfortunately I cannot collate the ranges into one sheet as the format of
the report cannot be changed.

Any ideas?

Thanks

pb