limitation of dynamic charting technique

[PBcorn]

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

 

[Shane Devenshire]

Hi,

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

 

[Chieftain of the Carpet Crawlers]

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

When you made the sheet that has the errant references, did you happen
to cut and paste it from elsewhere?

 

[Jon Peltier]

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